Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table test1_local as select 'hello';

SELECT 1


--this is all just to create a similar table on another db on db<>fiddle
create extension dblink;
select dblink_connect('db_2_dblink', 
                      'dbname=template1');

select dblink_exec('db_2_dblink',
$long_literal$ 
create table test2_db_2 as select 'hi';
$long_literal$);

select dblink_disconnect('db_2_dblink');

CREATE EXTENSION




dblink_connect


OK



SELECT 1




dblink_exec


SELECT 1



SELECT 1




dblink_disconnect


OK



SELECT 1


select string_agg(column_name,' text, ') from information_schema.columns 
  where table_schema='information_schema' and table_name='tables';


string_agg


table_catalog text, table_schema text, table_name text, table_type text, self_referencing_column_name text, reference_generation text, user_defined_type_catalog text, user_defined_type_schema text, user_defined_type_name text, is_insertable_into text, is_typed text, commit_action



SELECT 1


create extension postgres_fdw;

CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (dbname 'template1');
CREATE USER MAPPING FOR current_user
        SERVER foreign_server
        OPTIONS (user 'postgres');
CREATE FOREIGN TABLE db_2_information_schema_tables (
        table_catalog text, table_schema text, table_name text, table_type text, self_referencing_column_name text, reference_generation text, user_defined_type_catalog text, user_defined_type_schema text, user_defined_type_name text, is_insertable_into text, is_typed text, commit_action text)
  SERVER foreign_server
  OPTIONS (schema_name 'information_schema', 
           table_name 'tables');

CREATE EXTENSION



CREATE SERVER



CREATE USER MAPPING



CREATE FOREIGN TABLE


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
--union all
--select 'db_3', * from db_2_information_schema_tables;

SELECT 421


refresh materialized view mv_pg_all_tables;

select * from mv_pg_all_tables where table_schema='public' order by random();

REFRESH MATERIALIZED VIEW




dbname
table_catalog
table_schema
table_name
table_type
self_referencing_column_name
reference_generation
user_defined_type_catalog
user_defined_type_schema
user_defined_type_name
is_insertable_into
is_typed
commit_action


local
postgres
public
pg_temp_16452
BASE TABLE
null
null
null
null
null
YES
NO
null

local
postgres
public
db_2_information_schema_tables
FOREIGN
null
null
null
null
null
YES
NO
null

db_2
template1
public
test2_db_2
BASE TABLE
null
null
null
null
null
YES
NO
null

local
postgres
public
test1_local
BASE TABLE
null
null
null
null
null
YES
NO
null



SELECT 4

dbname	table_catalog	table_schema	table_name	table_type	self_referencing_column_name	reference_generation	user_defined_type_catalog	user_defined_type_schema	user_defined_type_name	is_insertable_into	is_typed	commit_action
local	postgres	public	pg_temp_16452	BASE TABLE	null	null	null	null	null	YES	NO	null
local	postgres	public	db_2_information_schema_tables	FOREIGN	null	null	null	null	null	YES	NO	null
db_2	template1	public	test2_db_2	BASE TABLE	null	null	null	null	null	YES	NO	null
local	postgres	public	test1_local	BASE TABLE	null	null	null	null	null	YES	NO	null