Yes, however, the documentation would be a lot clearer if it said "copies
all constraints except foreign keys". I've made this known.
At any rate, I've attached a new version of the function that now does copy
the foreign keys. Let me know if I missed anything else.
On Thu, Sep 10, 2015 at 9:09 AM, Igor Neyman <[email protected]> wrote:
>
>
>
>
> *From:* Melvin Davidson [mailto:[email protected]]
> *Sent:* Wednesday, September 09, 2015 4:48 PM
> *To:* Igor Neyman <[email protected]>
> *Cc:* [email protected]
> *Subject:* Re: [GENERAL] clone_schema function
>
>
>
> Thanks Igor,
>
> hmm, apparently the "INCLUDING CONSTRAINTS' option of "CREATE TABLE' has a
> glitch and only includes the primary key.
>
> I also noticed that INCLUDING ALL generates an error, so I'll have to
> report that also.
>
> I'll go eat some crow and work on a fix to add all constraints in the
> meantime.
>
>
>
>
>
> It’s not a bug.
>
> According to docs:
>
> “Not-null constraints are always copied to the new table. CHECK
> constraints will only be copied if INCLUDING CONSTRAINTS is specified;
> other types of constraints will never be copied.”
>
> So, FK constraints are not supposed to be copied.
>
>
>
> Regards,
>
> Igor Neyman
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
-- Function: clone_schema(text, text)
-- DROP FUNCTION clone_schema(text, text);
CREATE OR REPLACE FUNCTION clone_schema(
source_schema text,
dest_schema text)
RETURNS void AS
$BODY$
-- This function will clone all sequences, tables, data, views & functions
from any existing schema to a new one
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema');
DECLARE
src_oid oid;
tbl_oid oid;
func_oid oid;
object text;
buffer text;
srctbl text;
default_ text;
column_ text;
qry text;
dest_qry text;
v_def text;
seqval bigint;
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_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) ;
-- 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
EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' ||
quote_ident(object);
srctbl := quote_ident(source_schema) || '.' || quote_ident(object);
EXECUTE 'SELECT last_value, max_value, start_value, increment_by,
min_value, cache_value, log_cnt, is_cycled, is_called
FROM ' || quote_ident(source_schema) || '.' ||
quote_ident(object) || ';'
INTO sq_last_value, sq_max_value, sq_start_value,
sq_increment_by, sq_min_value, sq_cache_value, sq_log_cnt, sq_is_cycled,
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 ' || sq_min_value
|| ' CACHE ' || sq_cache_value
|| sq_cycled || ' ;' ;
buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value || ', ' ||
sq_is_called || ');' ;
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 := dest_schema || '.' || quote_ident(object);
EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' ||
quote_ident(source_schema) || '.' || quote_ident(object)
|| ' INCLUDING ALL)';
-- Insert records from source table
EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' ||
quote_ident(source_schema) || '.' || quote_ident(object) || ';';
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(%' || quote_ident(source_schema) ||
'%::regclass)'
LOOP
EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET
DEFAULT ' || default_;
END LOOP;
END LOOP;
-- add FK constraint
FOR qry IN
SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || rn.relname || '
ADD CONSTRAINT ' || 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;
-- Create views
FOR object IN
SELECT table_name::text,
view_definition
FROM information_schema.views
WHERE table_schema = quote_ident(source_schema)
LOOP
buffer := 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);
EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || ' AS ' || v_def || ';' ;
END LOOP;
-- Create functions
FOR func_oid IN
SELECT oid
FROM pg_proc
WHERE pronamespace = src_oid
LOOP
SELECT pg_get_functiondef(func_oid) INTO qry;
SELECT replace(qry, source_schema, dest_schema) INTO dest_qry;
EXECUTE dest_qry;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION clone_schema(text, text)
OWNER TO postgres;
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
