create view for all tables from multiple databases

Question

I have seveval databases in a postgresql db server

each database has its own username/password, and only access by nominate schema (in fact, the schema is same name of the username)

I'd like to create a all_tables view, so i can do query later.

I did some research on dblink extension, but still can't make it work.

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 commands run fine, but when query in all_tables, there is no any tables

Answer 1

1. dblink_fdw could do, but it's recommended that you use the native postgres_fdw instead.
2. You don't need to both dblink_connect and set up the server and user mapping. You use the former to manage connections to call dblink_execute, the latter to set up the foreign data wrapper and subsequently, a foreign table that uses them.
3. What you did doesn't automatically add visibility of everything in the other databases to your information_schema. You can either run queries through dblink_exec and work with their output, or set up the foreign table or import foreign schema for many tables at once. The second option most resembles what you expected it to do.

Here's an example showing how to set up an information_schema.tables but for multiple databases. 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;

Unfortunately, create foreign table doesn't accept (like another_table) syntax so you need to list the column names and types on your own.

In different versions of PostgreSQL Built-in tables and views can have different column names in different order, which you need to bear in mind whenever you link them as foreign table: here is an example of view definition re-use going wrong.