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.

swapping column values in postgres

simply want to swap all column value we can achieve by,
1) renaming column
2) by creating one more temporary column

if we like to swap column say fname and lname to be swapped by condition on id > 300 on table personal

CREATE OR REPLACE FUNCTION swap() RETURNS integer
AS
$BODY$
DECLARE
-- declare anyvariable over here
VFname varchar;
VLasswd varchar ;
VSwap personal%rowtype;
-- create cursor
CSwap cursor for select id,fname,lname from personal where id > 300;
BEGIN
open CSwap;
loop fetch next from CSwap into VSwap;
exit when not found;
raise notice 'fname : % , lname : %',VSwap.fname,VSwap.lname;
update personal set fname=VSwap.lname,lname=VSwap.fname where id = VSwap.id;
end loop;
RETURN 1;
END;
$BODY$
LANGUAGE 'plpgsql'

call the function by
select swap();

explain : cursor CSwap hold the value of fname, lanme and id greater then 300
when looping cursor just updating fname and lanme using cursor variable VSwap.lanme and VSwap.fname by matching id with VSwap.id