enhanced postgresql clone_schema() function with trigger + foreign key support

clone_schema_fn.sql

CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text) RETURNS void AS
$$

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;
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
  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;
 
END;
 
$$ LANGUAGE plpgsql VOLATILE;

tks, this works well for me

This script doesn't do constraints properly. It refers back to the public schema as seen here:
-- Table: tenant5.archived_dataset_file

-- DROP TABLE tenant5.archived_dataset_file;

CREATE TABLE tenant5.archived_dataset_file
(
archived_dataset_file_id integer NOT NULL DEFAULT nextval('archived_dataset_file_archived_dataset_file_id_seq'::regclass),
name character varying(255),
dataset_id integer,
export_format_id integer,
file_reference character varying(1000),
run_time integer,
file_size integer,
date_created timestamp(6) without time zone,
owner_id integer,
CONSTRAINT archived_dataset_file_pkey PRIMARY KEY (archived_dataset_file_id),
CONSTRAINT fk_archived_reference_dataset FOREIGN KEY (dataset_id)
REFERENCES public.dataset (dataset_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_archived_reference_export_f FOREIGN KEY (export_format_id)
REFERENCES public.export_format (export_format_id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (
OIDS=FALSE
);
ALTER TABLE tenant5.archived_dataset_file
OWNER TO clinica;

thanks @rocksfrow for hard work :)

One thing that I noticed should be changed:

 EXECUTE 'ALTER TABLE '|| buffer ||' ADD CONSTRAINT '|| constraint_name_ ||' '|| constraint_def_;

to

 EXECUTE 'ALTER TABLE '|| buffer ||' ADD CONSTRAINT "'|| constraint_name_ ||'" '|| constraint_def_;

I put " around constraint_name_

Why? because postgres makes constraint_name_ to lower case, in case u have foreign key like that FK_foo_BAR_id postgres will copy that as fk_foo_bar_id

case sensitive relationship names are not maintained ... for example i have a table User and User_Role .. the script try user_role and user with result in error relation not found.

Thanks! It helps me after on day of Google search.