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

i1icjdpr 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(197)

我有seveval数据库在一个postgresql db服务器
每个数据库都有自己的用户名/密码，并且只能通过指定模式访问（实际上，模式与用户名同名）
我想创建一个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

来源：https://stackoverflow.com/questions/77425773/create-view-for-all-tables-from-multiple-databases

1条答案

按热度按时间

ujv3wf0j1#

dblink_fdw可以，但建议您使用原生postgres_fdw。
1.您不需要同时设置dblink_connect和server以及user mapping，您可以使用前者来管理连接以调用dblink_execute，使用后者来设置外部数据 Package 器，然后使用foreign table来使用它们。
1.您所做的并不会自动将其他数据库中所有内容的可见性添加到您的information_schema。您可以通过dblink_exec运行查询并处理其输出，或者一次为许多表设置foreign table或import foreign schema。第二个选项与您期望的最相似。
下面是一个示例，展示了如何为多个数据库设置information_schema.tables。Demo at db<>fiddle：

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时，你需要记住这一点：这里是一个视图定义重用出错的例子。

赞(0）回复(0）举报 2023-11-18

我来回答

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

1条答案

相关问题

热门标签

最新问答