호출 예시

CALL CREATE_BACKUP_SCHEMA('FROM_SCHEMA', 'TO_SCHEMA');

정의

DROP PROCEDURE IF EXISTS CREATE_BACKUP_SCHEMA;
CREATE PROCEDURE CREATE_BACKUP_SCHEMA
(
    _from_schema_name TEXT, 
      _to_schema_name TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    loop_record RECORD;
    table_nm TEXT;
BEGIN
    EXECUTE(FORMAT('CREATE SCHEMA %s', _to_schema_name));

    FOR loop_record IN
        SELECT tablename FROM pg_tables
        WHERE schemaname = _from_schema_name
    LOOP
        EXECUTE(FORMAT('CREATE TABLE %2$s.%1$s AS SELECT * FROM %3$s.%1$s', loop_record.tablename, _to_schema_name, _from_schema_name));
    END LOOP;
END; $$;