为多个数据库中的所有表创建视图-解网

问：

我在postgresql数据库服务器中有seveval数据库

每个数据库都有自己的用户名/密码，并且只能通过指定架构进行访问（实际上，架构与用户名同名）

我想创建一个all_tables视图，以便稍后可以进行查询。

我对扩展做了一些研究，但仍然无法使其工作。dblink

SELECT dblink_connect('source_db_1', 'postgres://db_1:[email protected]/db_1?sslmode=require');
CREATE SERVER source_db_1 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr 'psqlserver.postgres.database.azure.com', dbname 'db_1');
CREATE USER MAPPING FOR current_user SERVER source_db_1 OPTIONS (user 'db_1', password 'password');

SELECT dblink_connect('source_db_2', 'postgres://db_2:[email protected]/db_2?sslmode=require');
CREATE SERVER source_db_2 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr 'psqlserver.postgres.database.azure.com', dbname 'db_2');
CREATE USER MAPPING FOR current_user SERVER source_db_2 OPTIONS (user 'db_2', password 'password');

CREATE OR REPLACE VIEW all_tables_in_db_1 AS
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'db_1';

CREATE OR REPLACE VIEW all_tables_in_db_2 AS
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'db_2';

CREATE OR REPLACE VIEW all_tables_in_db_3 AS
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'db_3';


CREATE OR REPLACE VIEW all_tables AS
SELECT * FROM all_tables_in_db_1
UNION ALL
SELECT * FROM all_tables_in_db_2
UNION ALL
SELECT * FROM all_tables_in_db_3;

所有命令都运行良好，但是当查询时，没有任何表all_tables

PostgreSQL Azure 视图 psql dblink

1）非常确定顺序错误，不正确。有关正确用法，请参阅dblink_connect。2）我没有看到您的视图实际上连接到其他数据库，只是连接到本地数据库并按table_schema进行过滤。3）我没有看到您授予用户对外部服务器的权限。SELECT dblink_connect()

答：

0赞 Zegarek 11/6/2023 #1

dblink_fdw可以，但建议您改用本机postgres_fdw。
您不需要同时设置和。使用前者管理要调用的连接，后者设置外部数据包装器，然后设置使用它们的外部表。dblink_connectserveruser mappingdblink_execute
您执行的操作不会自动将其他数据库中所有内容的可见性添加到您的 .您可以通过dblink_exec运行查询并使用其输出，也可以一次为多个表设置或导入外部架构。第二个选项最类似于您期望它执行的操作。information_schemaforeign table

以下示例显示了如何为多个数据库设置 but。db<>fiddle 演示：information_schema.tables

CREATE EXTENSION postgres_fdw;

CREATE SERVER foreign_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (hostaddr 'psqlserver.postgres.database.azure.com', 
           dbname 'db_2');
CREATE USER MAPPING FOR current_user
  SERVER foreign_server
  OPTIONS (user 'db_2', 
           password 'password');
CREATE FOREIGN TABLE db_2_information_schema_tables
  (table_catalog name, table_schema name, table_name name, table_type character varying, self_referencing_column_name name, reference_generation character varying, user_defined_type_catalog name, user_defined_type_schema name, user_defined_type_name name, is_insertable_into character varying, is_typed character varying, commit_action character varying)
  SERVER foreign_server
  OPTIONS (schema_name 'information_schema', 
           table_name 'tables');

CREATE MATERIALIZED VIEW mv_pg_all_tables AS
SELECT 'local' as dbname, * FROM information_schema.tables
UNION ALL
SELECT 'db_2'           , * FROM db_2_information_schema_tables;

不幸的是，它不接受语法，因此您需要自己列出列名和类型。create foreign table(like another_table)

在不同版本的 PostgreSQL 中，内置表和视图可以以不同的顺序具有不同的列名，每当将它们链接为：下面是视图定义重用出错的示例。foreign table

您只需要从每个数据库中链接一个。这是一个系统视图，列出了所有数据库范围的视图。只要 db 的版本匹配，该系统视图的结构也会匹配。当然，这是假设你的目标是在所有数据库中看到一个所有表的列表，而不是它们的内容的某种串联。information_schema.tables

上一个：SwiftUI 删除特定区域中的视图拖放

下一个：ViewHolder 在使用 Kotlin 绑定时无法识别 textview

为多个数据库中的所有表创建视图

create view for all tables from multiple databases

评论

评论