My database layout needs to create new schema for each new customer. Currently I use internal function I found on the net and modified a little bit.
CREATE FUNCTION copy_schema(
source_schema character varying,
target_schema character varying,
copy_data boolean)
RETURNS integer AS
$BODY$
DECLARE
t_ex integer := 0;
s_ex integer := 0;
src_table character varying;
trg_table character varying;
BEGIN
if (select 1 from pg_namespace where nspname = source_schema) THEN
-- we have defined target schema
s_ex := 1;
END IF;
IF (s_ex = 0) THEN
-- no source schema exist
RETURN 0;
END IF;
if (select 1 from pg_namespace where nspname = target_schema) THEN
-- we have defined target schema need to sync all table layout
t_ex := 1;
ELSE
EXECUTE 'CREATE SCHEMA '||target_schema||' AUTHORIZATION user';
END IF;
FOR src_table IN
SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = source_schema
LOOP
trg_table := target_schema||'.'||src_table;
EXECUTE
'CREATE TABLE ' || trg_table || ' (LIKE ' || source_schema || '.' || src_table || ' INCLUDING ALL)';
IF (copy_data = true) THEN
EXECUTE 'INSERT INTO ' || trg_table || '(SELECT * FROM ' || source_schema || '.' || src_table || ')';
END IF;
END LOOP;
return t_ex;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
The problem with this script is that tables in new schema continue to use source schema's sequences. Is there way using sql statements (or other reliable way) to get fresh copy of sequences (or even another reliable way to duplicate entire schema) for the newly created tables?
And so after some thinking I went along with updating sql function mentioned in my first post so now it looks like this:
CREATE FUNCTION copy_schema(
source_schema character varying,
target_schema character varying,
copy_data boolean)
RETURNS integer AS
$BODY$
DECLARE
t_ex integer := 0;
s_ex integer := 0;
src_table character varying;
trg_table character varying;
BEGIN
if (select 1 from pg_namespace where nspname = source_schema) THEN
-- we have defined target schema
s_ex := 1;
END IF;
IF (s_ex = 0) THEN
-- no source schema exist
RETURN 0;
END IF;
if (select 1 from pg_namespace where nspname = target_schema) THEN
-- we have defined target schema need to sync all table layout
t_ex := 1;
ELSE
EXECUTE 'CREATE SCHEMA '||target_schema||' AUTHORIZATION user';
END IF;
FOR src_table IN
SELECT table_name
FROM information_schema.TABLES
WHERE table_schema = source_schema
LOOP
trg_table := target_schema||'.'||src_table;
EXECUTE 'CREATE TABLE ' || trg_table || ' (LIKE ' || source_schema || '.' || src_table || ' INCLUDING ALL)';
EXECUTE 'CREATE SEQUENCE ' || trg_table || '_id_seq OWNED BY '||trg_table || '.id';
EXECUTE 'ALTER TABLE ' || trg_table || ' ALTER COLUMN id SET DEFAULT nextval('''|| trg_table || '_id_seq''::regclass)';
IF (copy_data = true) THEN
EXECUTE 'INSERT INTO ' || trg_table || '(SELECT * FROM ' || source_schema || '.' || src_table || ')';
END IF;
END LOOP;
return t_ex;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
This is not quite universal solution for everybody, but as all my tables in schema have serial field named id, it fits me.
Version suggested by @erwin-brandstetter with dump / hack dump file / restore dump file back again is commonly seen on the forums as the way to go.
In case of dedicated server it could work, in case of shared hosting (or need of less dependencies on outside scripts) the way of internal function seems better.