`clone_schema` for PostgreSQL 15+

README.md

clone_schema for PostgreSQL 15+

Updated version of the posted sql referenced in clone schema on the PostgreSQL wiki to work with PostgreSQL 15+.

Cloning

SELECT clone_schema('public', 'pappyclone', TRUE);

Dropping

Included is a drop_schema_objects function, which is supposed to be a safer way of dropping a schema without running the risk of blowing away objects in other schema using CASCADE. You still need to DROP SCHEMA after running it to get rid of the empty schema shell itself.

SELECT drop_schema_objects('pappyclone');
DROP SCHEMA pappyclone;

clone_schema.function.sql

CREATE OR REPLACE FUNCTION clone_schema(
    source_schema text,
    dest_schema text,
    include_recs boolean)
  RETURNS void AS
$BODY$
DECLARE
  src_oid          oid;
  func_oid         oid;
  object           text;
  buffer           text;
  default_         text;
  column_          text;
  qry              text;
  dest_qry         text;
  v_def            text;
  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_is_called     boolean;
  sq_is_cycled     boolean;
  sq_cycled        text;
  idx_stmt         text;
  typ_stmt         text;
BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
    FROM pg_namespace
   WHERE nspname = 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 1
    FROM pg_namespace
   WHERE nspname = dest_schema;
  IF FOUND THEN
    RAISE NOTICE 'dest schema % already exists!', dest_schema;
    RETURN;
  END IF;

  EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema);

  -- 1. Clone user-defined types (including enums)
  FOR typ_stmt IN
    SELECT format(
      CASE
        WHEN t.typtype = 'e' THEN
          -- Enum type
          'CREATE TYPE %I.%I AS ENUM (%s);'
        ELSE
          -- Composite type
          'CREATE TYPE %I.%I AS (%s);'
      END,
      dest_schema,
      t.typname,
      CASE
        WHEN t.typtype = 'e' THEN (
          SELECT string_agg(quote_literal(enumlabel), ', ' ORDER BY enumsortorder)
          FROM pg_enum
          WHERE enumtypid = t.oid
        )
        ELSE (
          SELECT string_agg(
                   quote_ident(a.attname) || ' ' ||
                   pg_catalog.format_type(a.atttypid, a.atttypmod),
                   ', '
                 )
          FROM pg_attribute a
          WHERE a.attrelid = t.typrelid AND a.attnum > 0 AND NOT a.attisdropped
        )
      END
    )
    FROM pg_type t
    JOIN pg_namespace n ON n.oid = t.typnamespace
    WHERE n.nspname = source_schema
      AND t.typtype IN ('c','e') -- composite and enum
      AND t.typcategory <> 'A'   -- skip array types
      AND NOT t.typname LIKE '_%' -- skip array types (redundant)
  LOOP
    EXECUTE typ_stmt;
  END LOOP;

  -- 2. Clone sequences
  FOR object IN
    SELECT c.relname
      FROM pg_class c
           JOIN pg_namespace n ON n.oid = c.relnamespace
     WHERE c.relkind = 'S'
       AND n.nspname = source_schema
  LOOP
    EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object);

    -- Get sequence parameters from pg_sequences
    SELECT max_value, start_value, increment_by, min_value, cache_size, cycle
    INTO sq_max_value, sq_start_value, sq_increment_by, sq_min_value, sq_cache_value, sq_is_cycled
    FROM pg_sequences
    WHERE schemaname = source_schema AND sequencename = object;

    -- Get last_value and is_called from the actual sequence
    EXECUTE format('SELECT last_value, is_called FROM %I.%I', source_schema, object)
    INTO sq_last_value, sq_is_called;

    IF sq_is_cycled THEN
      sq_cycled := 'CYCLE';
    ELSE
      sq_cycled := 'NO CYCLE';
    END IF;

    EXECUTE
      'ALTER SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object)
      || ' INCREMENT BY ' || sq_increment_by
      || ' MINVALUE '     || sq_min_value
      || ' MAXVALUE '     || sq_max_value
      || ' START WITH '   || sq_start_value
      || ' RESTART '      || (CASE WHEN include_recs THEN sq_last_value ELSE sq_start_value END)
      || ' CACHE '        || sq_cache_value
      || ' ' || sq_cycled;

    buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
    IF include_recs THEN
      EXECUTE 'SELECT setval(''' || buffer || ''', ' || sq_last_value || ', ' || (CASE WHEN sq_is_called THEN 'true' ELSE 'false' END) || ');';
    ELSE
      EXECUTE 'SELECT setval(''' || buffer || ''', ' || sq_start_value || ', ' || (CASE WHEN sq_is_called THEN 'true' ELSE 'false' END) || ');';
    END IF;
  END LOOP;

  -- 3. Clone tables
  FOR object IN
    SELECT table_name
      FROM information_schema.tables
     WHERE table_schema = 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)';

    IF include_recs THEN
      EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';';
    END IF;

    -- Fix column defaults that refer to sequences in the old schema
    FOR column_, default_ IN
      SELECT column_name, column_default
      FROM information_schema.columns
      WHERE table_schema = dest_schema
        AND table_name = object
        AND column_default LIKE '%nextval(%' || quote_literal(source_schema) || '%::regclass)%'
    LOOP
      EXECUTE 'ALTER TABLE ' || buffer
          || ' ALTER COLUMN ' || quote_ident(column_)
          || ' SET DEFAULT ' || replace(default_, source_schema, dest_schema);
    END LOOP;
  END LOOP;

  -- 4. Add FK constraints
  FOR qry IN
    SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname)
           || ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' ' || pg_get_constraintdef(ct.oid) || ';'
    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;

  -- 5. Clone indexes (except primary keys, unique, and exclusion constraints, which are already handled)
  FOR idx_stmt IN
    SELECT 'CREATE '
           || CASE WHEN idx.indisunique THEN 'UNIQUE ' ELSE '' END
           || 'INDEX '
           || quote_ident(indexrel.relname || '_clone') -- avoid name collisions
           || ' ON '
           || quote_ident(dest_schema) || '.' || quote_ident(tab.relname)
           || ' USING ' || am.amname
           || ' (' ||
           array_to_string(
             ARRAY(
               SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE)
               FROM generate_subscripts(idx.indkey, 1) as k
               ORDER BY k
             ), ', '
           )
           || ')'
           || CASE WHEN pg_get_expr(idx.indpred, idx.indrelid) IS NOT NULL THEN
                ' WHERE ' || pg_get_expr(idx.indpred, idx.indrelid)
              ELSE '' END
           || ';'
    FROM pg_index idx
    JOIN pg_class tab ON tab.oid = idx.indrelid
    JOIN pg_class indexrel ON indexrel.oid = idx.indexrelid
    JOIN pg_namespace nsp ON nsp.oid = tab.relnamespace
    JOIN pg_am am ON am.oid = indexrel.relam
    WHERE nsp.nspname = source_schema
      AND NOT idx.indisprimary
      AND NOT idx.indisunique -- skip PK/unique constraints handled elsewhere
      AND indexrel.relkind = 'i'
      AND NOT indexrel.relname LIKE 'pg_%'
  LOOP
    EXECUTE idx_stmt;
  END LOOP;

  -- 6. Clone views
  FOR object, v_def IN
    SELECT table_name, view_definition
      FROM information_schema.views
     WHERE table_schema = source_schema
  LOOP
    buffer := quote_ident(dest_schema) || '.' || quote_ident(object);

    -- Replace schema references in view definition
    EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || ' AS '
      || replace(v_def, source_schema || '.', dest_schema || '.');
  END LOOP;

  -- 7. Clone functions
  FOR func_oid IN
    SELECT oid
      FROM pg_proc
     WHERE pronamespace = src_oid
  LOOP
    SELECT pg_get_functiondef(func_oid) INTO qry;
    SELECT regexp_replace(qry, source_schema, dest_schema, 'g') INTO dest_qry;
    BEGIN
      EXECUTE dest_qry;
    EXCEPTION
      WHEN OTHERS THEN
        RAISE NOTICE 'Failed to create function %: %', func_oid, SQLERRM;
    END;
  END LOOP;

  RETURN;
END;
$BODY$
  LANGUAGE plpgsql
  VOLATILE
  COST 100;

ALTER FUNCTION clone_schema(text, text, boolean)
  OWNER TO postgres;

drop_schema_objects.function.sql

CREATE OR REPLACE FUNCTION drop_schema_objects(schema_name text)
RETURNS void AS
$BODY$
DECLARE
    obj text;
    func_sig text;
BEGIN
    -- Drop all views
    FOR obj IN
        SELECT table_name
        FROM information_schema.views
        WHERE table_schema = schema_name
    LOOP
        EXECUTE 'DROP VIEW IF EXISTS ' || quote_ident(schema_name) || '.' || quote_ident(obj) || ' CASCADE';
    END LOOP;

    -- Drop all foreign keys from tables in the schema
    FOR obj IN
        SELECT tc.table_name || '.' || rc.constraint_name
        FROM information_schema.table_constraints tc
        JOIN information_schema.referential_constraints rc ON tc.constraint_name = rc.constraint_name
        WHERE tc.table_schema = schema_name
          AND tc.constraint_type = 'FOREIGN KEY'
    LOOP
        -- Remove FK constraints individually
        EXECUTE 'ALTER TABLE ' || quote_ident(schema_name) || '.' ||
                quote_ident(split_part(obj, '.', 1)) ||
                ' DROP CONSTRAINT IF EXISTS ' || quote_ident(split_part(obj, '.', 2));
    END LOOP;

    -- Drop all tables (CASCADE, but only within the schema)
    FOR obj IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = schema_name
          AND table_type = 'BASE TABLE'
    LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(schema_name) || '.' || quote_ident(obj) || ' CASCADE';
    END LOOP;

    -- Drop all sequences
    FOR obj IN
        SELECT sequence_name
        FROM information_schema.sequences
        WHERE sequence_schema = schema_name
    LOOP
        EXECUTE 'DROP SEQUENCE IF EXISTS ' || quote_ident(schema_name) || '.' || quote_ident(obj) || ' CASCADE';
    END LOOP;

    -- Drop all functions
    FOR func_sig IN
        SELECT
            'DROP FUNCTION IF EXISTS '
            || quote_ident(schema_name) || '.' || quote_ident(p.proname)
            || '('
            || pg_get_function_identity_arguments(p.oid)
            || ') CASCADE'
        FROM pg_proc p
        JOIN pg_namespace n ON n.oid = p.pronamespace
        WHERE n.nspname = schema_name
    LOOP
        EXECUTE func_sig;
    END LOOP;

    -- Drop all types (including enums and composites)
    FOR obj IN
        SELECT typname
        FROM pg_type t
        JOIN pg_namespace n ON n.oid = t.typnamespace
        WHERE n.nspname = schema_name
          AND t.typtype IN ('c', 'e') -- composite, enum
          AND t.typcategory <> 'A'
          AND NOT t.typname LIKE '_%'
    LOOP
        EXECUTE 'DROP TYPE IF EXISTS ' || quote_ident(schema_name) || '.' || quote_ident(obj) || ' CASCADE';
    END LOOP;

    -- Optionally: Drop materialized views, domains, etc.

    RETURN;
END;
$BODY$
LANGUAGE plpgsql;