Return Inserted, Updated and Deleted rows/record of the Table in PostgreSQL

 Query to get return last Inserted, Updated and Deleted record in PostgreSQL

this will work PostgreSQL 9.1 and above

it return the updated row, it update the values in the table and also return the updated record.

WITH tmp AS 
  (
   UPDATE login_user SET user_name = '1'||user_name 
    where username = 'abs'
   RETURNING *
  )
SELECT * FROM tmp;

Also return specific column instead of full row in RETURNING clause

WITH tmp AS 
  (
   UPDATE login_user SET user_name = '1'||user_name 
    where username = 'abs'
   RETURNING  id, user_name
  )
SELECT * FROM tmp;

Insert all the deleted row into another table by

WITH tmp AS  (
   DELETE  FROM login_user WHERE user_name ='abc'
    RETURNING *  )
INSET INTO t1 select * from tmp;

Convert Multiple Row output from SELECT query into Single Row in PostgreSQL

Here is the simple query to convert the result of select query from multiple rows in a table to display as single row,


Simple SELECT query

SELECT str_empno FROM employee;

will return the output like,


Output:

str_empno
-------------
4400100476
4400100563
4400101130
4400101432
4400101501
4400101527
4400101724

by using array_agg and array_to_string function in PostgreSQL,  we can get aggregates/combine/concatenate multiple rows into one row

SELECT array_to_string( array_agg(str_empno),',' ) as  empno FROM employee;

this will return 
Output:

str_empno
-------------

4400100476,4400100563,4400101130,4400101432,4400101501,4400101527,4400101724  

Display all Sunday in the given date in postgresql

here is the query to display all the Sunday amid in given two date

SELECT 
    cast( mydate as date) as sunday
FROM
    generate_series(timestamp '2011-01-01', '2011-03-01', '1 day') AS g(mydate) 
    
WHERE
    EXTRACT(DOW FROM mydate) = 0;

DOW = 0 means SUNDAY.

output of the above query is

   sunday
--------
2-1-2011
9-1-2011
16-1-2011
23-1-2011
30-1-2011
6-2-2011
13-2-2011
20-2-2011
27-2-2011 

if you want to get display all the week day amid in given date

in where condition use  !=

SELECT 
   cast( mydate as date) as sunday.
FROM
    generate_series(timestamp '2011-01-01', '2011-03-01', '1 day') AS g(mydate) 
    
WHERE
    EXTRACT(DOW FROM mydate) != 0;

above query will return all the day of the week except sunday

-----------------------------------------------------------------------------------------------------------------------------------

alternative for cast in select clause we can also use

    EXTRACT (Day from mydate ) || '-' ||  EXTRACT (month from mydate ) || '-' || EXTRACT (year from mydate ) 

Add the column only if not exist on PostgreSQL

here the function to add the column to the table only if column does not exist.

create or replace function addcol(schemaname varchar, tablename varchar, colname varchar, coltype varchar)
returns varchar 
language 'plpgsql'
as $$
declare 
    col_name varchar ;
begin 
      execute 'select column_name from information_schema.columns  where  table_schema = ' ||
      quote_literal(schemaname)||' and table_name='|| quote_literal(tablename) || '   and    column_name= '|| quote_literal(colname)    
      into   col_name ;   

      raise info  ' the val : % ', col_name;
      if(col_name is null ) then 
          col_name := colname;
          execute 'alter table ' ||schemaname|| '.'|| tablename || ' add column '|| colname || '  ' || coltype; 
      else
           col_name := colname ||' Already exist';
      end if;
return col_name;
end;
$$

function would require argument schema, table, column and data type

if the column exist it returns, 'column Already exist '  else 
return the added column.




 select addcol('masters','m_approve_status', 'test1', 'integer');




create a sequence that start with max id of another table in PostgreSQL

recently I had a chance to work around getting value from different table and merge into single table as part of the migration.

during the same I have to create a sequence that starts with maximum id of another table and increment by,

any way that can be created by simple as
 

SELECT max(col_name) from table_name  -- it 'll rtn max id i.e 89
CREATE SEQUENCE seqtest INCREMENT BY 1 STARTS WITH 89;

the same can be written in function, to shun the half human interaction.

the function below will find the max id from given table and assign it into sequence (Assign max(id) to Sequence)

the function find_max()  would return the max id  i.e currval of the sequence.

create or replace function find_max_seq(tablename varchar, colname varchar) 
returns int 
language 'plpgsql'
as 
$$
declare 
 maxno integer;
begin 
 execute 'select  max('||colname|| ')  from ' || tablename  into  maxno ;   
 --execute 'select max(cast( '||colname|| ' as int))  from ' || tablename  into  maxno ;   
 raise info  ' the val : % ', maxno;
 execute 'create sequence seqtest increment by 1 start with  ' || maxno; 
return maxno;
end;
$$

 SELECT  findmax('masters.m_approve_status', 'status_id')

PostgreSQL : No operator matches the given name and argument type(s).

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts

this information related to PostgreSQL version,

Issue :: DATE data type with pattern matching operator (LIKE) in version 8.3 later.

Date data type with LIKE operator works fine for the prior to 8.3 version.

for ex : SELECT name FROM employee WHERE date_of_birth LIKE '2007-01-19%'; // older version works fine

query will display all the name of date_of_birth falls on 2007-01-19.

but version 8.3 will gives hint like
> HINT: No operator matches the given name and argument type(s). You
> might need to add explicit type casts.

and
This isn't a bug. 8.3 removes a bunch of implicit casts to text which
led to inappropriate behaviours. Prior to 8.3, your timestamps was
casts to text implicitly.

to get the same query output you may use "date_trunc" function its works like normal "trunc" function.

for ex : SELECT name FROM employee WHERE date_trunc('day',date_of_birth) = '2007-01-19';
query will round with date and display all the name.

List all duplicate Foreign Key constraint in PostgreSQL

Below SQL query will return all the referential integrity (Foreign Key ) constraint  in a current database.

This statement return  all the foreign key constraint including duplicate foreign key constraint that's differ in  constraint name for the same   reference (Parent)  table & column for the child table.

example :

 CONSTRAINT fk964cbaa6283cf475 FOREIGN KEY (str_fa_request_id)
      REFERENCES panmydesk4400.d_fa_indent_form (str_fa_request_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,

CONSTRAINT m_cash_purchase_str_fa_request_id_fkey FOREIGN KEY (str_fa_request_id)
      REFERENCES panmydesk4400.d_fa_indent_form (str_fa_request_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,

above example clearly shows that both foreign key constraint ( fk964cbaa6283cf475 and m_cash_purchase_str_fa_request_id_fkey )

 defined in child table of column

str_fa_request_id

refers the same reference table & column.

panmydesk4400.d_fa_indent_form (str_fa_request_id)

SELECT
    pc.conname as constraint_name, 
    --conrelid as child_table_id,   
    pclsc.relname as child_table,
    --pc.conkey as child_column_id,
    pac.attname as child_column,
    --confrelid as parent_table_id,
    pclsp.relname as parent_table,
    --pc.confkey as parent_column_id,
    pap.attname as parent_column,   
    nspname as schema_name
FROM 
    (
    SELECT
         connamespace,conname, unnest(conkey) as "conkey", unnest(confkey)
          as "confkey" , conrelid, confrelid, contype
     FROM
        pg_constraint
    ) pc
    JOIN pg_namespace pn ON pc.connamespace = pn.oid
    -- and pn.nspname = 'panmydesk4400'
    JOIN pg_class pclsc ON pc.conrelid = pclsc.oid
    JOIN pg_class pclsp ON      pc.confrelid = pclsp.oid
    JOIN pg_attribute pac ON pc.conkey = pac.attnum    and pac.attrelid =       pclsc.oid
    JOIN pg_attribute pap ON pc.confkey = pap.attnum and pap.attrelid = pclsp.oid

ORDER BY pclsc.relname

note : 

•       line start with -- indicate commented 

run the above statement  would return the following result  as output,

FIND THE DIFFERENCE IN 2 SCHEMA & GENERATE DDL AFTER COMPARING THE SCHEMAS

Here the plpgsql utility to compare schema to find the difference and generate DDL statement.

This function return
            1) List of tables which is exist in one  schema
            2) Generate the  DDL statement for schema synchronization.
( This statement is useful when we synchronize the schema in same database) 
            3) Generate the table backup and restore statement
( This statement is useful when we synchronize the schema in different database, for this we need to backup of the differences table and restore into another database )


before run this function in your database check it has plpgsql by
SELECT * FROM pg_language
if your database not supported plpgsql, add by using
CREATE LANGUAGE plpgsql



Function accept two schema of same database


CREATE OR REPLACE FUNCTION fn_compare_schema(p_fst_schema character varying, p_snd_schema character varying)
RETURNS character varying
language plpgsql
AS $$
-- DECLARE VARIABLE
DECLARE
V_FstSchema varchar(150);
V_SndSchema varchar(150);
i integer;
VtblNot varchar = '';
VtblCrt varchar = '';
VtblCrossCreate varchar = '';
Vrtn varchar = '';
-- DECLARE CURSOR
Ctbl_list1 refcursor;
Ctbl1 pg_stat_user_tables.relname%type;
Vtblkcu information_schema.key_column_usage;
BEGIN
V_FstSchema := p_fst_schema;
V_sndSchema := p_snd_schema;
i :=0;
loop -- for passing both parameter by using single statment
-- CREATE AND OPEN CURSOR TO HOLD THE TABLES IN ONE SCHEMA
VtblNot := 'TABLES NOT IN SCHEMA : ' || V_sndSchema ||chr(10);
raise info 'TABLES NOT IN SCHEMA : % ',V_sndSchema;
open Ctbl_list1 for execute '
select relname from pg_stat_user_tables where schemaname='||quote_literal(V_FstSchema)||'
except
select relname from pg_stat_user_tables where schemaname='||quote_literal(V_sndSchema);
loop fetch next from Ctbl_list1 into Ctbl1;
exit when not found;
raise info 'TABLE NAME : % ',Ctbl1;
--ADD ALL THE TABLES IN ONE SCHEMA AND NOT IN ANOTHER
VtblNot := chr(10)||VtblNot||';'|| chr(10) || V_FstSchema||'.'||Ctbl1 ;
-- ADD CREATE DDL STATEMENT (var used VtblCrt, only Structure of the table )
VtblCrt := VtblCrt|| chr(10) || 'create table '|| V_SndSchema||'.'||Ctbl1 ||' as select * from '|| V_FstSchema||'.'||Ctbl1 ||' where 1=0; ' ;
VtblCrossCreate := VtblCrossCreate || chr(10) || 'pg_dump sourcedbname -t '||V_FstSchema ||'.'||Ctbl1 ||' -c -s | psql -h localhost targetdbname;';
--raise info 'Vtblcrt : %',Vtblcrt ;
end loop;
Vrtn = vrtn || chr(10) || VtblNot;
Vrtn = chr(10)||Vrtn ||chr(10)||VtblCrt;
Vrtn = chr(10)||Vrtn ||chr(10)||VtblCrossCreate ;
VtblCrt = '';
VtblCrossCreate = '';
close Ctbl_list1;
exit when i=1;
i:=1;
--THIS TIME THE SCHEMA WOULD INTERCHANGE LOOP WILL EXECUTE       --AGAIN
V_FstSchema := p_snd_schema;
V_sndSchema := p_fst_schema;
end loop; --for comparing two schema in single statment
raise info 'VRTN : %; ',Vrtn;
RETURN Vrtn;
end;
$$

run above function by ( execute with write result to file option in pgadmin3),

SELECT fn_compare_schema('dbschema1','dbschema2') 

in psql mode

COPY ( SELECT fn_compare_schema('dbschema1','dbschema2') ) To '/tmp/ddloutput.csv' With CSV;

OR

psql dbname
psql>\o '/tmp/ddloutput.csv'
psql>SELECT  fn_compare_schema('dbschema1','dbschema2');
psql>\q

OR


psql -d dbname -t -A -F"," -c "SELECT fn_compare_schema('dbschema1','dbschema2') " > ddloutput.csv

output like

TABLES NOT IN SCHEMA : panmydesk2200;

panmydesk4400.d_item_reject;
panmydesk4400.d_preaudit_cc_map;

create table panmydesk2200.d_item_reject as select * from panmydesk4400.d_item_reject where 1=0;
create table panmydesk2200.d_preaudit_cc_map as select * from panmydesk4400.d_preaudit_cc_map where 1=0;

pg_dump sourcedbname -t panmydesk4400.d_item_reject -c -s | psql -h localhost targetdbname;
pg_dump sourcedbname -t panmydesk4400.d_preaudit_cc_map -c -s | psql -h localhost targetdbname;

note :

sourcedbname → where the table's actually reside 

targetdbname  → to where you have to add the table from source db

Hadoop : Bad connection to FS. command aborted.

I've successfully configured Hadoop on Debian, I got following error message while running the command,
hduser@boss:/opt/hadoop-0.21.0/bin$ ./hadoop fs  -ls /

localhost/127.0.0.1:54310. Already tried 0 time(s).
12/08/16 15:46:12 INFO ipc.Client: Retrying connect to server: 
localhost/127.0.0.1:54310. Already tried 1 time(s).
............
Bad connection to FS. command aborted. 

solution for the above issues is,

just check whether all the port are up and running by
hduser@boss:/opt/hadoop-0.21.0/bin$ netstat -nltp

tcp  0  0 127.0.0.1:8020  0.0.0.0:*   LISTEN      9469/java       
tcp        0  127.0.0.1:8021    0.0.0.0:*  LISTEN   9879/java  
tcp   0   0 0.0.0.0:37301     0.0.0.0:*     LISTEN   9879/java 
tcp     0      0 0.0.0.0:50070  0.0.0.0:*    LISTEN  9469/java  
............ 

in my case, the port which was mentioned in core-site.xml not running, then I figured it out, 

 fs.default.name

  was mentioned in both hdfs-site.xml and core-site.xml, 

in hdfs-site.xml

 fs.default.name
 localhost:9000
 
 
    dfs.replication
    1
  

and in core-site.xml

  hadoop.tmp.dir
  /app/hadoop/tmp



  fs.default.name
  hdfs://localhost:8020

after I removed the property from the hdfs-site.xml, it is working fine.  

List the Table / Object size in Postgresql Database.

Here is the query to find the largest Table in current database


SELECT u.schemaname || '.' ||c.relname AS relation, c.relpages, t.relpages AS toastpages, (c.relpages+coalesce(t.relpages,0))*8 as KB
FROM PG_CLASS c
LEFT OUTER JOIN PG_CLASS t ON c.reltoastrelid=t.oid
RIGHT OUTER JOIN PG_STAT_USER_TABLES u ON u.relid = c.oid

ORDER BY  4 DESC LIMIT 10;

Above query will result the TOP 10 Object, If you would like to view all the table just remove LIMIT keyword from the query. 

Alter the column with same name in multiple table in PostgreSQL

If you have column that replicated in 500+ tables then to some extent you really do have to execute 800 update statements. But you do not have to type out all 500+ update statements -- it is better to have a function that help you to create the targeted update statements.

this function alter the size of the replicated column, 

CREATE  FUNCTION fn_alter_tbl(schema_name varchar, column_name varchar, length integer) RETURNS varchar
    LANGUAGE plpgsql
    AS $$
DECLARE
-- declare any variable over here
Vname varchar(150);
VnameId varchar(150);
Vcnt int = 0;
VColCnt int;
RtnTblList varchar = '';
-- create cursor, will hold all the tables in given schema
Ctbl_list cursor for select * from pg_stat_user_tables where schemaname=schema_name order by relname;
Vtbl pg_stat_user_tables%rowtype;
BEGIN
open Ctbl_list;
-- iterate cursor to get table
loop fetch next from Ctbl_list into Vtbl;
exit when not found;
Vname = Vtbl.relname;
VnameId = Vtbl.relid;
--process each table to check given column exist in the table, if it is qry will return 1 and alter --statement will be executed
EXECUTE 'select count(*) from pg_attribute where attrelid ='||VnameId||' and attname='||quote_literal(column_name)  into VColCnt;
if  VColCnt != 0 then
EXECUTE 'alter table spms_fixedassets.'|| Vname ||' alter column ' || column_name || ' type  varchar('||length||')';
vcnt = vcnt +1;
RtnTblList = RtnTblList ||Vname || ','||chr(10);
end if;
end loop;
raise notice ' RtnTblList :: %',RtnTblList;
raise notice ' COUNT :: % ',Vcnt;
RETURN RtnTblList;
END;
$$;

 Execute the function

 select * from fn_alter_tbl('fixedassets', 'str_empno',20)

Result :

It return list of altered table 

ex:

d_indent_form,
d_indenteditems,
d_iss_mat_register,
d_indentor_cc,
d_item_verify_issues,
d_mapping_cc, 


above function alter the column size that exist on multiple table, this simply reduce the DBA work by
--> need not find the table with column 
--> write alter statement for each table




parameter  :

1) schema_name : name of the schema in which do you like to 

change the table's column, If you don't created any schema PostgreSQL use by default “public” schema

2) column_name : name of the column, this may exist in multiple table of above schema

3) length : new length of the above column

 

Note : This will work only for the type varchar, and function will return list of altered table

QUERY TO RETUN MOSTLY USED / ACCESSED TABLE IN POSTGRESQL

Below query will return most accessed table in PostgreSQL database,

SELECT c.relname as table_name, c.reltuples::numeric as no_of_records, c.relpages as  no_of_pages,
       t.seq_scan as tbl_seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,      t.n_tup_ins, t.n_tup_upd, t.n_tup_del,
       COALESCE(t.idx_tup_fetch,0) + COALESCE(t.seq_tup_read,0) as total_read
    FROM pg_stat_user_tables as t inner join pg_class as c
    ON (t.relid = c.oid)
    WHERE c.relkind ='r'
    ORDER BY total_read desc
    limit  5


it return top 5 mostly used table, if you want this query to return more-then 5 table change the limit 5 to desired No.of table

Compare Database Schema in POSTGRESQL using plpgsql function

Here the plpgsql utility to compare tables and find the difference between given schema of the database in PostgreSQL, And it returns differences table name as well as DDL statement for schema synchronization.

Function accept two schema of same database and it return statement that contains, table differences in schema, and automatically generated Data Definition Language (DDL) scripts ( for synchronization ) that you can use to update the schema of the destination to match the schema of the source.

before run this function in your database check it has plpgsql by
SELECT * FROM pg_language
if your database not supported plpgsql, add by using
CREATE LANGUAGE plpgsql


CREATE OR REPLACE FUNCTION fn_compare_tbl(p_fst_schema character varying, p_snd_schema character varying)
RETURNS character varying
language plpgsql
AS
$$
-- DECLARE VARIABLE
DECLARE
V_FstSchema varchar(150);
V_SndSchema varchar(150);
i integer;
VtblNot varchar = '';
VtblCrt varchar = '';
Vrtn varchar = '';
-- DECLARE CURSOR
Ctbl_list1 refcursor;
Ctbl1 pg_stat_user_tables.relname%type;
Vtblkcu information_schema.key_column_usage;
BEGIN
V_FstSchema := p_fst_schema;
V_sndSchema := p_snd_schema;
i :=0;
loop -- for passing both parameter by using single statment
-- CREATE AND OPEN CURSOR TO HOLD THE TABLES IN ONE SCHEMA
VtblNot := 'TABLES NOT IN SCHEMA : ' || V_sndSchema ||chr(10);
raise info 'TABLES NOT IN SCHEMA : % ',V_sndSchema;
open Ctbl_list1 for execute '
select relname from pg_stat_user_tables where schemaname='||quote_literal(V_FstSchema)||'
except
select relname from pg_stat_user_tables where schemaname='||quote_literal(V_sndSchema);
loop fetch next from Ctbl_list1 into Ctbl1;
exit when not found;
raise info 'TABLE NAME : % ',Ctbl1;
--ADD ALL THE TABLES IN ONE SCHEMA AND NOT IN ANOTHER
VtblNot := chr(10)||VtblNot||';'|| chr(10) || V_FstSchema||'.'||Ctbl1 ;
-- ADD CREATE DDL STATEMENT (var used VtblCrt, only Structure of the table )
VtblCrt := VtblCrt|| chr(10) || 'create table '|| V_SndSchema||'.'||Ctbl1 ||' as select * from '|| V_FstSchema||'.'||Ctbl1 ||' where 1=0; ' ;
raise info 'Vtblcrt : %',Vtblcrt ;
end loop;
Vrtn = vrtn || chr(10) || VtblNot;
Vrtn = chr(10)||Vrtn ||chr(10)||VtblCrt;
VtblCrt = '';
close Ctbl_list1;
exit when i=1;
i:=1;
--THIS TIME THE SCHEMA WOULD INTERCHANGE LOOP WILL EXECUTE AGAIN
V_FstSchema := p_snd_schema;
V_sndSchema := p_fst_schema;
end loop; --for comparing two schema in single statment
raise info 'VRTN : %; ',Vrtn;
RETURN Vrtn;
end;
$$

run above function by ( execute with write result to file option ),

SELECT fn_compare_tbl('dbschema1','dbschema2')

here both schema should be in same database, mostly it used in multi-tenant (single database with multiple schema ) architecture. By using this function we can able to know what all are the new feature added in one then other schema.

query in above would return result thats look like,

TABLES NOT IN SCHEMA : dbschema2;

Table name :dbschema1.tempmedical;

DDL statement : create table dbschema2.tempmedical as select * from dbschema1.tempmedical where 1=0;

TABLES NOT IN SCHEMA : dbschema1;

Table name : dbschema2.test4function;

DDL statement : create table dbschema1.test4function as select * from dbschema2.test4function where 1=0; ,

List the all the constraint with statement

Below function fn_get_fk() used to return the FOREIGN KEY constraints with create/drop statement.
This function will minimize PostgreSQL DBA's work to find all the referencing table to drop the constraint one by one manually.
Table name and Column names are the parameter for this function, it generate and return query to, CREATE/DROP CONSTRAINT statement for the referencing table (child table) for the given referenced table(parent table).

CREATE or REPLACE FUNCTION fn_get_fk(tbl_name varchar, col_name varchar) RETURNS varchar
LANGUAGE plpgsql
AS $
-- DECLARE VARIABLE
DECLARE
Vtbl varchar(150);
Vqrys varchar(150);
Vcol varchar(150);
Vcon varchar(150);
Vqryd varchar(300);
Vqrya varchar(300);
Vsch varchar(150);
VqryAdd varchar(30000) = '';
VqryDrp varchar(30000) = '';
Vrtn varchar;
-- CREATE CURSOR
Ctbl_list refcursor;
Ctblcon information_schema.table_constraints %rowtype;
Vtblkcu information_schema.key_column_usage;
BEGIN
-- OPEN CURSOR WITH REFERENCING TABLE FOR GIVEN PARENT TABLE
open Ctbl_list for execute 'SELECT *
FROM information_schema.table_constraints tc
right JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_catalog = ccu.constraint_catalog
AND tc.constraint_schema = ccu.constraint_schema
AND tc.constraint_name = ccu.constraint_name
and ccu.table_name in ('||quote_literal(tbl_name)||')
and ccu.column_name in ('||quote_literal(col_name)||')
WHERE lower(tc.constraint_type) in (''foreign key'');' ;
loop fetch next from Ctbl_list into Ctblcon ;
exit when not found;
Vtbl = Ctblcon.table_name;
Vcon = Ctblcon.constraint_name;
Vsch = Ctblcon.constraint_schema;
--GENERATE DROP CONSTRAINT STATEMENT
Vqryd = 'alter table '|| Vsch||'.'||Vtbl ||' drop constraint ' || Vcon;
VqryDrp = VqryDrp ||';'||chr(10)||Vqryd;
raise info 'Qry : %; ',Vqryd;
--GET REFRENCING COLUMN NAME
Vqrys = 'select * from information_schema.key_column_usage where constraint_name='||quote_literal(Vcon) ;
EXECUTE Vqrys into Vtblkcu;
-- GENERATE ADD CONSTRAINT STATEMENT
Vqrya = 'alter table '|| Vsch||'.'||Vtbl ||' add constraint ' || Vcon ||' foreign key('||Vtblkcu.column_name||')
references personal.m_pis_master(str_empno)';
raise notice 'add constraint : %; ',Vqrya;
VqryAdd = VqryAdd || chr(10) || Vqrya || ';';
end loop;
-- CONCATENATE BOTH ADD & DROP STATEMENT
Vrtn ='DROP CONSTRAINT : '|| VqryDrp ||chr(10)||'ADD CONSTRAINT : '||chr(10)||VqryAdd;
RETURN Vrtn;
END;
$;

in pgAdmin, to Execute the function with write output to file option,
SELECT * FROM fn_get_fk('employee_master','str_empno')

in command mode
copy (select * from fn_get_fk('employee_master','str_empno')) to '/root/Desktop/Constraint.txt'

this will return all the foreign key constraint which is referencing the column str_empno on employee_master table.

output looks like

DROP CONSTRAINT :
alter table personal.m_authority drop constraint m_authority_str_empno_fkey;
alter table personal.m_pis drop constraint m_pis_str_empno_fkey;
alter table tours.d_tour_info drop constraint d_tour_info_str_empno_fkey
ADD CONSTRAINT :
alter table personal.m_authority add constraint m_authority_str_empno_fkey foreign key(str_empno) references personal.m_pis_master(str_empno);
alter table personal.m_pis add constraint m_pis_str_empno_fkey foreign key(str_empno) references personal.m_pis_master(str_empno);
alter table tours.d_tour_info add constraint d_tour_info_str_empno_fkey foreign key(str_empno) references personal.m_pis_master(str_empno),

Move tables from multiple schemas to single schema in PostgreSql

• Here the simple function will move the table from multiple schema into single schema.
• This function actually move table from existing schema into new schema, i.e you would not find table inside existing schema once this function is called.
• All the table will be moved into new schema with structure including all constraints and the values.
  
• Name of the schema to be passed to function via the argument

CREATE OR REPLACE FUNCTION fn_move_tbl(P_schema varchar) RETURNS integer
AS
$BODY$
DECLARE
-- declare any variable over here
Vname varchar(150);
-- create cursor
Ctbl_list cursor for select * from pg_stat_user_tables order by relname;
-- declare table type variable
Vtbl pg_stat_user_tables%rowtype;
BEGIN
OPEN Ctbl_list;
loop fetch next from Ctbl_list into Vtbl;
exit when not found;
Vname = Vtbl.schemaname ||'.'|| Vtbl.relname ;
raise notice 'Old table :: % ,New table:: %.%',Vname,P_schema,Vtbl.relname ;
EXECUTE 'alter table ' || Vname || ' set schema '||P_schema ;
end loop;
RETURN 1;
END;
$BODY$
LANGUAGE 'plpgsql'

function would be created

Before calling above function you must create schema to which you want to move all the table. for creating schema
CREATE SCHEMA tenant1

Call the function by
SELECT fn_move_tbl('tenant1') ;
Here tenant1 is the name of schema to which all the table will move. If schema does not exist the function will through error like " ERROR: schema "tenant" does not exist"

Example :

1) Create database, add plpgsql lang for creating function and restore the database backup




2) Database test restored with 9 different schema.



3) CREATE SCHEMA tenant1

Schema tenant1 created with empty tables. Existing schema master having 56 tables.

4) SELECT fn_move_tbl('tenant1');

All the tables from 9 schema are moved into tenant1 schema, tenat1 schema having 134 tables.