-- Function: public.clone_schema(text, text, boolean)

-- DROP FUNCTION public.clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION public.clone_schema(
    source_schema text,
    dest_schema text,
    include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2018-07-03 by Melvin Davidson
--  Added ' OVERRIDING USER VALUE' for INSERT records
--
--  This function will clone all sequences, tables, indexes, rules, triggers, 
--  data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);

DECLARE
  src_oid          oid;
  tbl_oid          oid;
  seq_oid          oid;
  func_oid         oid;
  owner_id         oid;
  con_oid          oid;
  v_path           text;
  v_func           text;
  v_args           text;
  v_conname        text;
  v_rule           text;
  v_trig           text;
  object           text;
  buffer           text;
  srctbl           text;
  default_         text;
  v_column         text;
  qry              text;
  dest_qry         text;
  v_def            text;
  v_stat           integer;
  seqval           bigint;
  seq_name         name;
  sq_type          oid;
  sq_last_value    bigint;
  sq_max_value     bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value     bigint;
  sq_cache_value   bigint;
  sq_log_cnt       bigint;
--  sq_is_called     boolean;
  sq_is_cycled     boolean;
  sq_typname       name;
  seq_cycled       text;
  seq_owner        name;  
--  sq_cycled        char(10);
  
BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
    FROM pg_namespace
   WHERE nspname = quote_ident(source_schema);
   
  IF NOT FOUND
    THEN 
      RAISE NOTICE 'source schema % does not exist!', source_schema;
      RETURN ;
  END IF;

  -- Check that dest_schema does not yet exist
  PERFORM nspname 
    FROM pg_namespace
   WHERE nspname = quote_ident(dest_schema);
   
  IF FOUND
    THEN 
      RAISE NOTICE 'dest schema % already exists!', dest_schema;
      RETURN ;
  END IF;

  EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;
   
  -- Add schema comment
  SELECT description INTO v_def
    FROM pg_description
   WHERE objoid = src_oid
     AND objsubid = 0;

  IF FOUND 
    THEN 
      EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' || quote_literal(v_def);
  END IF;

  SET search_path TO dest_schema, source_schema, 'public', pg_catalog;  
  SELECT current_schemas(TRUE) INTO v_path;
  RAISE NOTICE 'search path = %', v_path;

  -- Create sequences
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
     SELECT sequence_name::text 
      FROM information_schema.sequences
     WHERE sequence_schema = quote_ident(source_schema)

  LOOP
    
    SELECT oid, relowner into seq_oid, owner_id
	  FROM pg_class 
	 WHERE relname = quote_ident(object)
	   AND relkind = 'S';

	SELECT seqtypid,
	  	   seqstart,
		   seqincrement,
	       seqmax,
	       seqmin,
		   seqcache,
		   seqcycle
	  INTO sq_type, 
		   sq_start_value,
	       sq_increment_by,
	       sq_max_value,
	       sq_min_value,
	       sq_cache_value,
           sq_is_cycled
	  FROM pg_sequence
     WHERE seqrelid = seq_oid;
 
    SELECT typname INTO sq_typname
	  FROM pg_type 
	 WHERE oid = sq_type;
	
	SELECT rolname INTO seq_owner
	  FROM pg_authid WHERE oid = owner_id;
	
--    EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object);
--    srctbl := quote_ident(source_schema) || '.' || quote_ident(object);

    IF sq_is_cycled 
	  THEN 
	    seq_cycled = ' CYCLE'; 
	  ELSE 
	    seq_cycled = ' NO CYCLE';
	END IF;
	
	EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object)
	     || ' AS ' || sq_typname
         || ' INCREMENT ' ||  sq_increment_by
         || ' MINVALUE ' || sq_min_value
		 || ' MAXVALUE ' || sq_max_value
         || ' START WITH ' || sq_start_value 
		 || ' CACHE ' || sq_cache_value
		 || seq_cycled;
--         || ' OWNED BY ' || seq_owner;
	
/*    buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
    IF include_recs 
        THEN
            EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value || ', ' || sq_is_called || ');' ; 
    ELSE
            EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value || ', ' || sq_is_called || ');' ;
    END IF;
*/

  -- add sequence comments
    SELECT oid INTO tbl_oid
      FROM pg_class 
     WHERE relkind = 'S'
       AND relnamespace = src_oid
       AND relname = quote_ident(object);

    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = tbl_oid
       AND objsubid = 0;

    IF FOUND
      THEN      
        EXECUTE 'COMMENT ON SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object)
                 || ' IS ''' || v_def || ''';';
    END IF;


  END LOOP;

-- Create tables 
  FOR object IN
    SELECT TABLE_NAME::text 
      FROM information_schema.tables 
     WHERE table_schema = quote_ident(source_schema)
       AND table_type = 'BASE TABLE'

  LOOP
    buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
    EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object) 
        || ' INCLUDING ALL)';

    -- Add table comment
    SELECT oid INTO tbl_oid
      FROM pg_class 
     WHERE relkind = 'r'
       AND relnamespace = src_oid
       AND relname = quote_ident(object);

    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = tbl_oid
       AND objsubid = 0;

    IF FOUND
      THEN      
        EXECUTE 'COMMENT ON TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(object)
                 || ' IS ''' || v_def || ''';';
    END IF;
   
    IF include_recs 
      THEN 
      -- Insert records from source table
      EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ' OVERRIDING USER VALUE;';
    END IF;
 
    FOR v_column, default_ IN
      SELECT column_name::text, 
             REPLACE(column_default::text, quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.' )
        FROM information_schema.COLUMNS 
       WHERE table_schema = dest_schema 
         AND TABLE_NAME = object 
         AND column_default LIKE 'nextval(%' || quote_ident(source_schema) || '%::regclass)'
    LOOP
      EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || v_column || ' SET DEFAULT ' || default_;

    END LOOP;

  END LOOP;

  -- set column statistics
  FOR tbl_oid, srctbl IN 
    SELECT oid, relname
      FROM pg_class
     WHERE relnamespace = src_oid
       AND relkind = 'r'

  LOOP

    FOR v_column, v_stat IN         
      SELECT attname, attstattarget
        FROM pg_attribute 
       WHERE attrelid = tbl_oid
         AND attnum > 0
       
    LOOP

      buffer := quote_ident(dest_schema) || '.' || quote_ident(srctbl);	 
--      RAISE EXCEPTION 'ALTER TABLE % ALTER COLUMN % SET STATISTICS %', buffer, v_column, v_stat::text;
      EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || quote_ident(v_column) || ' SET STATISTICS ' || v_stat || ';';

    END LOOP;
  END LOOP;

--  add FK constraint
  FOR qry IN
    SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname) 
                          || ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' ' || replace(pg_get_constraintdef(ct.oid), quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.') || ';'
      FROM pg_constraint ct
      JOIN pg_class rn ON rn.oid = ct.conrelid
     WHERE connamespace = src_oid
       AND rn.relkind = 'r'
       AND ct.contype = 'f'
         
    LOOP
      EXECUTE qry;

    END LOOP;

    -- Add constraint comment
    FOR con_oid IN
      SELECT oid
        FROM pg_constraint
       WHERE conrelid = tbl_oid

    LOOP
      SELECT conname INTO v_conname
        FROM pg_constraint
       WHERE oid = con_oid;

      SELECT description INTO v_def
        FROM pg_description
       WHERE objoid = con_oid;
       
       IF FOUND
          THEN 
            EXECUTE 'COMMENT ON CONSTRAINT ' || v_conname || ' ON ' || quote_ident(dest_schema) || '.' || quote_ident(object) 
                    || ' IS ''' || v_def || ''';';
       END IF; 

    END LOOP;


-- Create views 
  FOR object IN
    SELECT table_name::text,
           view_definition 
      FROM information_schema.views
     WHERE table_schema = quote_ident(source_schema)

  LOOP
    buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
    SELECT view_definition INTO v_def
      FROM information_schema.views
     WHERE table_schema = quote_ident(source_schema)
       AND table_name = quote_ident(object);

    SELECT REPLACE(v_def, source_schema, dest_schema) INTO v_def; 
--    RAISE NOTICE 'view def, % , source % , dest % ',  v_def, source_schema, dest_schema;
    
    EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || ' AS ' || v_def || ';' ;

    -- Add comment
    SELECT oid INTO tbl_oid
      FROM pg_class 
     WHERE relkind = 'v'
       AND relnamespace = src_oid
       AND relname = quote_ident(object);

    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = tbl_oid
       AND objsubid = 0;

    IF FOUND
      THEN      
        EXECUTE 'COMMENT ON VIEW ' || quote_ident(dest_schema) || '.' || quote_ident(object)
                 || ' IS ' || quote_literal(v_def);
    END IF;


  END LOOP;

-- Create functions 
  FOR func_oid IN
    SELECT oid, proargnames
      FROM pg_proc 
     WHERE pronamespace = src_oid

  LOOP      
    SELECT pg_get_functiondef(func_oid) INTO qry;
    SELECT proname, oidvectortypes(proargtypes) INTO v_func, v_args
      FROM pg_proc 
     WHERE oid = func_oid;
    SELECT replace(qry, quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.') INTO dest_qry;
    EXECUTE dest_qry;

    -- Add function comment
    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = func_oid
       AND objsubid = 0;

    IF FOUND
      THEN
--        RAISE NOTICE 'func_oid %, object %,  v_args %',  func_oid::text, quote_ident(object), v_args;               
        EXECUTE 'COMMENT ON FUNCTION ' || quote_ident(dest_schema) || '.' || quote_ident(v_func) || '(' || v_args || ')'
                 || ' IS ' || quote_literal(v_def) ||';' ;
    END IF;


  END LOOP;

  -- add Rules
    SET search_path TO dest_schema;
    
    FOR v_def IN
      SELECT definition 
        FROM pg_rules
       WHERE schemaname = quote_ident(source_schema)
     
    LOOP

    IF v_def IS NOT NULL
        THEN 
          SELECT replace(v_def, source_schema || '.', dest_schema || '.') INTO v_def;
          EXECUTE ' ' || v_def;
      END IF;
    END LOOP;

  -- add triggers
  FOR v_def IN 
    SELECT pg_get_triggerdef(oid)
      FROM pg_trigger
     WHERE tgname NOT LIKE 'RI_%'
       AND tgrelid IN (SELECT oid
                         FROM pg_class
                        WHERE relkind = 'r'
                          AND relnamespace = src_oid)

  LOOP      

    SELECT replace(v_def, source_schema || '.', dest_schema || '.') INTO dest_qry;
    EXECUTE dest_qry;
    
  END LOOP;
  --  Disable inactive triggers
  --  D = disabled
  FOR tbl_oid IN 
    SELECT oid
      FROM pg_trigger
     WHERE tgenabled = 'D'
       AND tgname NOT LIKE 'RI_%'
       AND tgrelid IN (SELECT oid
                         FROM pg_class
                        WHERE relkind = 'r'
                          AND relnamespace = src_oid)
  LOOP
    SELECT t.tgname, c.relname INTO object, srctbl
      FROM pg_trigger t
      JOIN pg_class c ON c.oid = t.tgrelid
     WHERE t.oid = tbl_oid;
    						      
    IF FOUND 
      THEN 	
	EXECUTE 'ALTER TABLE ' || dest_schema || '.' || srctbl || ' DISABLE TRIGGER ' || object || ';';
    END IF;

  END LOOP;

      -- Add index comment

  FOR tbl_oid IN 
    SELECT oid 
      FROM pg_class 
     WHERE relkind = 'i'
       AND relnamespace = src_oid
		 
  LOOP
    
    SELECT relname INTO object
      FROM pg_class
     WHERE oid = tbl_oid;
    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = tbl_oid
       AND objsubid = 0;

    IF FOUND
      THEN      
        EXECUTE 'COMMENT ON INDEX ' || quote_ident(dest_schema) || '.' || quote_ident(object)
                 || ' IS ''' || v_def || ''';';
    END IF;
	
    END LOOP;
    
  -- add rule comments
  FOR con_oid IN 
    SELECT oid, * 
      FROM pg_rewrite
     WHERE rulename <> '_RETURN'::name

  LOOP

    SELECT rulename, ev_class INTO v_rule, tbl_oid
      FROM pg_rewrite
     WHERE oid = con_oid;

    SELECT relname INTO object
      FROM pg_class
     WHERE oid = tbl_oid
       AND relkind = 'r';
     
    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = con_oid
       AND objsubid = 0;
       
    IF FOUND 
      THEN 
        EXECUTE 'COMMENT ON RULE ' || v_rule || ' ON ' || quote_ident(dest_schema) || '.' || object || ' IS ' || quote_literal(v_def);
    END IF;

  END LOOP;

  -- add trigger comments
  FOR con_oid IN 
    SELECT oid, * 
      FROM pg_trigger
     WHERE tgname NOT LIKE 'RI_%'

  LOOP

    SELECT tgname, tgrelid INTO v_trig, tbl_oid
      FROM pg_trigger
     WHERE oid = con_oid;

    SELECT relname INTO object
      FROM pg_class
     WHERE oid = tbl_oid
       AND relkind = 'r';
     
    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = con_oid
       AND objsubid = 0;
       
    IF FOUND 
      THEN 
        EXECUTE 'COMMENT ON TRIGGER ' || v_trig || ' ON ' || quote_ident(dest_schema) || '.' || object || ' IS ' || quote_literal(v_def);
    END IF;

  END LOOP;
    
  RETURN; 

 
END;
 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.clone_schema(text, text, boolean)
  OWNER TO postgres;
COMMENT ON FUNCTION public.clone_schema(text, text, boolean) IS 'Duplicates sequences, tables, indexes, rules, triggers, data(optional),
     views & functions from the source schema to the destination schema';