PostgreSQL多数据库创建all_tables视图后无数据问题求助

你虽然配置了dblink_fdw的远程服务器和用户映射，但后续创建的all_tables_in_db_1等视图，依然在查询当前本地数据库的information_schema.tables，完全没有关联到你配置的远程数据库，所以视图返回空结果。

下面提供两种可行的修正方式，任选其一即可：

方式一：直接在视图中用dblink查询远程数据库 #

利用dblink函数直接拉取远程数据库的schema表信息，修正后的视图创建语句如下：

-- 修正db_1的视图
CREATE OR REPLACE VIEW all_tables_in_db_1 AS
SELECT table_name
FROM dblink('source_db_1', 'SELECT table_name FROM information_schema.tables WHERE table_schema = ''db_1''') 
AS t(table_name text);

-- 修正db_2的视图
CREATE OR REPLACE VIEW all_tables_in_db_2 AS
SELECT table_name
FROM dblink('source_db_2', 'SELECT table_name FROM information_schema.tables WHERE table_schema = ''db_2''') 
AS t(table_name text);

-- 若db_3已配置对应dblink连接，同理创建视图
CREATE OR REPLACE VIEW all_tables_in_db_3 AS
SELECT table_name
FROM dblink('source_db_3', 'SELECT table_name FROM information_schema.tables WHERE table_schema = ''db_3''') 
AS t(table_name text);

方式二：创建外部表映射远程information_schema，再基于外部表建视图 #

先创建外部表映射远程数据库的information_schema.tables，再基于这些外部表过滤出目标schema的表：

-- 为db_1创建外部表，映射远程的information_schema.tables
CREATE FOREIGN TABLE remote_db_1_tables (
    table_name text,
    table_schema text
) SERVER source_db_1 
OPTIONS (schema_name 'information_schema', table_name 'tables');

-- 基于外部表创建db_1的视图
CREATE OR REPLACE VIEW all_tables_in_db_1 AS
SELECT table_name
FROM remote_db_1_tables
WHERE table_schema = 'db_1';

-- 同理处理db_2
CREATE FOREIGN TABLE remote_db_2_tables (
    table_name text,
    table_schema text
) SERVER source_db_2 
OPTIONS (schema_name 'information_schema', table_name 'tables');

CREATE OR REPLACE VIEW all_tables_in_db_2 AS
SELECT table_name
FROM remote_db_2_tables
WHERE table_schema = 'db_2';

-- db_3按同样逻辑处理

• 确保远程数据库的用户（如db_1）拥有访问information_schema.tables的权限，默认情况下PostgreSQL用户都有这个权限，但若做了严格权限限制，需要额外授权。
• 检查dblink连接名称、服务器名称与配置的一致性，避免拼写错误导致连接失败。

内容的提问来源于stack exchange，提问作者Bill