1) Configure the dblink contrib module to the cluster.
And create the extension on all database.
Postgres=# create extension dblink ;
CREATE EXTENSION
DB1=# create extension dblink ;
CREATE EXTENSION
DB2=# create extension dblink ;
CREATE EXTENSION
2) Create the table to store ,output of your function.
postgres=# create table all_db_schemas(databasename name,schemaname name);
CREATE TABLE
3) Create the function on Postgres database.
create or replace function get_all_db_schemas() returns text as $$
declare
r record;
r1 record;
dbname1 name;
schemaname1 name;
done text default 'successes';
stmt1 text;
stmt3 text;
begin
for r in select datname from pg_stat_database
loop
dbname1 :=r.datname;
CONTINUE WHEN dbname1='template0';
stmt1 :='select * from dblink('
|| ''''
|| 'dbname='
|| dbname1
|| ''''
|| ','
|| ''''
|| 'select distinct schemaname from pg_statio_user_tables'
|| ''''
|| ') as t1(schemaname name)'
|| ';';
for r1 in execute stmt1
loop
schemaname1 :=r1.schemaname;
stmt3 := 'insert into all_db_schemas(databasename,schemaname) values ('
|| ''''
||dbname1
|| ''''
|| ','
|| ''''
|| schemaname1
|| ''''
|| ')'
|| ';' ;
execute stmt3;
end loop;
end loop;
return done;
end;
$$ language plpgsql;
4) Execute the function to get to collect all the schemas from all the databases on cluster.
postgres=# select get_all_db_schemas();
get_all_db_schemas
--------------------
successes
(1 row)
5) Check the data got inserted into table or not?
postgres=# select * from all_db_schemas;
databasename | schemaname
--------------+------------
postgres | public
postgres | s1
db1 | public
db2 | public
(4 rows)
And create the extension on all database.
Postgres=# create extension dblink ;
CREATE EXTENSION
DB1=# create extension dblink ;
CREATE EXTENSION
DB2=# create extension dblink ;
CREATE EXTENSION
2) Create the table to store ,output of your function.
postgres=# create table all_db_schemas(databasename name,schemaname name);
CREATE TABLE
3) Create the function on Postgres database.
create or replace function get_all_db_schemas() returns text as $$
declare
r record;
r1 record;
dbname1 name;
schemaname1 name;
done text default 'successes';
stmt1 text;
stmt3 text;
begin
for r in select datname from pg_stat_database
loop
dbname1 :=r.datname;
CONTINUE WHEN dbname1='template0';
stmt1 :='select * from dblink('
|| ''''
|| 'dbname='
|| dbname1
|| ''''
|| ','
|| ''''
|| 'select distinct schemaname from pg_statio_user_tables'
|| ''''
|| ') as t1(schemaname name)'
|| ';';
for r1 in execute stmt1
loop
schemaname1 :=r1.schemaname;
stmt3 := 'insert into all_db_schemas(databasename,schemaname) values ('
|| ''''
||dbname1
|| ''''
|| ','
|| ''''
|| schemaname1
|| ''''
|| ')'
|| ';' ;
execute stmt3;
end loop;
end loop;
return done;
end;
$$ language plpgsql;
4) Execute the function to get to collect all the schemas from all the databases on cluster.
postgres=# select get_all_db_schemas();
get_all_db_schemas
--------------------
successes
(1 row)
5) Check the data got inserted into table or not?
postgres=# select * from all_db_schemas;
databasename | schemaname
--------------+------------
postgres | public
postgres | s1
db1 | public
db2 | public
(4 rows)
No comments:
Post a Comment