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.