Create Clone Schema in postgre

Introduction

In an application, when we need to create a new DB schema for a new registered company, then we run several queries, create dump of existing schema after that restore that with new name, and with the same name also, this process is time taken, but now we can easily create schema using pgplsql.

Using the Code

We need to create a clone_schema function in public schema, and one dependent function which has dependency information for custom views, and function as shown below:

CREATE OR REPLACE FUNCTION init_functions()
  RETURNS TABLE(functionname character varying, seq numeric, inittype character varying) AS
$BODY$
declare
var_r record;
begin
 functionname:='<Function Name>'; seq:=1; inittype='func'; return next;
 functionname:='<Function Name>'; seq:=2; inittype='func'; return next;
 functionname:='<Function Name>'; seq:=3; inittype='func'; return next;
 functionname:='<Function Name>'; seq:=4; inittype='func'; return next;

functionname:='<View Name>'; seq:=101; inittype='view'; return next;
functionname:='<View Name>'; seq:=101; inittype='view'; return next;
functionname:='<View Name>'; seq:=101; inittype='view'; return next;
functionname:='<View Name>'; seq:=101; inittype='view'; return next;
end;
$BODY$
  LANGUAGE plpgsql ;

And create Clone_Schema function, with the following steps:

1. Replace Existing Schema
2. Create Schema
3. Create Sequences
4. Create Tables
5. Fix Sequence Defaults
6. Create Triggers
7. Reiterate Tables and Create Foreign Keys
8. Create User Defined Functions
9. Create Views
10. And Alter Extension "uuid-ossp" for new GUID

CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text)
  RETURNS void AS
$BODY$
 
DECLARE
  object text;
  buffer text;
  default_ text;
  column_ text;
  constraint_name_ text;
  constraint_def_ text;
  trigger_name_ text;
  trigger_timing_ text;
  trigger_events_ text;
  trigger_orientation_ text;
  trigger_action_ text;
  vw_name text;
  vw_body text;
  funct text;
  objetovw RECORD;
  objeto text;  
BEGIN
 
  -- replace existing schema
  EXECUTE 'DROP SCHEMA IF EXISTS ' || dest_schema || ' CASCADE';
 
  -- create schema
  EXECUTE 'CREATE SCHEMA ' || dest_schema ;
 
  -- create sequences
  FOR object IN
    SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema
  LOOP
    EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object;
  END LOOP;
 
  -- create tables
  FOR object IN
    SELECT table_name::text FROM information_schema.TABLES _
    WHERE table_schema = source_schema and is_insertable_into='YES'
  LOOP
    buffer := dest_schema || '.' || object;
 
    -- create table
    EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || _
    source_schema || '.' || object || _
    ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)';
 
    -- fix sequence defaults
    FOR column_, default_ IN
      SELECT column_name::text, REPLACE(column_default::text, source_schema||'.', _
      dest_schema||'.') FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND _
      table_name = object AND column_default LIKE 'nextval(%' || source_schema || '.%::regclass)'
    LOOP
      EXECUTE 'ALTER TABLE ' || buffer || _
      ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
    END LOOP;
 
    -- create triggers
    FOR trigger_name_, trigger_timing_, trigger_events_, trigger_orientation_, trigger_action_ IN
      SELECT trigger_name::text, action_timing::text, string_agg(event_manipulation::text, _
      ' OR '), action_orientation::text, action_statement::text _
      FROM information_schema.TRIGGERS WHERE event_object_schema=source_schema and _
      event_object_table=object GROUP BY trigger_name, action_timing, _
      action_orientation, action_statement
    LOOP
      EXECUTE 'CREATE TRIGGER ' || trigger_name_ || ' ' || trigger_timing_ || _
      ' ' || trigger_events_ || ' ON ' || buffer || _
      ' FOR EACH ' || trigger_orientation_ || ' ' || trigger_action_;
    END LOOP;
  END LOOP;
 
  -- reiterate tables and create foreign keys
  FOR object IN
    SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
  LOOP
    buffer := dest_schema || '.' || object;
 
    -- create foreign keys
    FOR constraint_name_, constraint_def_ IN
      SELECT conname::text, REPLACE(pg_get_constraintdef(pg_constraint.oid), _
      source_schema||'.', dest_schema||'.') FROM pg_constraint _
      INNER JOIN pg_class ON conrelid=pg_class.oid INNER JOIN pg_namespace ON _
      pg_namespace.oid=pg_class.relnamespace WHERE contype='f' _
      and relname=object and nspname=source_schema
    LOOP
      EXECUTE 'ALTER TABLE '|| buffer _
      ||' ADD CONSTRAINT '|| constraint_name_ ||' '|| constraint_def_;
    END LOOP;
  END LOOP;
 
--Create Functions
  FOR funct IN
  select     
     REPLACE(pg_get_functiondef(pp.oid), source_schema||'.', dest_schema||'.')  funct
    from pg_proc pp
    inner join pg_namespace pn on (pp.pronamespace = pn.oid)
    inner join pg_language pl on (pp.prolang = pl.oid)
    inner join INIT_functions()init on pp.proname=init.functionname and init.inittype='func'
    where pl.lanname NOT IN ('c','internal')  
  and pn.nspname = source_schema order by init.seq
   LOOP    
     EXECUTE funct||';';
   END LOOP;

-- Create Views
   FOR objetovw IN
        SELECT table_name,replace(view_definition,source_schema||_
        '.',dest_schema||'.') as view_definition
        FROM information_schema.VIEWS
         inner join INIT_functions() init _
         on table_name=init.functionname and init.inittype='view'
         WHERE table_schema = source_schema
         order by init.seq
    LOOP        
        buffer := dest_schema || '.' || objetovw.table_name;
        EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || _
        ' AS '||objetovw.view_definition;
    END LOOP;

  -- first create Extension in source schema "uuid-ossp" , _
  this extension is used for create guid
 
  EXECUTE 'ALTER EXTENSION "uuid-ossp" SET SCHEMA '||dest_schema;
END;
 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

If our existing Schema Name is "dev" and we want to create a new Schema, then only we execute this query to create new schema:

select clone_schema('dev', 'dev_new')

"dev_new" new schema is created.

Points of Interest

It's easy and easily implemented in application when we need to create a new schema.