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; ,