Re: Feature request/suggestion - CREATE SCHEMA LIKE

On Mon, Mar 17, 2008 at 9:01 PM, wstrzalka <wstrzalka(at)gmail(dot)com> wrote:
> Hi
>
> Features like CREATE DATABASE WITH TEMPLATE or CREATE TABLE LIKE
> are very usefull but it would be great to have such a feature on the
> mid-level too. I mean something CREATE SCHEMA LIKE that would copy all
> the template schema relations, etc...
> What do you think about it ? Would it be hard to implement ? Is it
> worth the effort ?

I think it is a bit too complicated for the backend -- you
have to copy functions, views, types along the tables.
And most importantly -- their dependencies (for the order in
which to create them). Chances are that user defined
functions won't work in new schema. Tricky to say the least.
Perhaps a pg_dump -s with an option to "rename" the schema
would be a better option to consider (sed(1) is a good friend,
but IMHO explicit option would be much better).

If you insist in putting it in database -- a PL/pgSQL
function would be the best approach IMHO, something along:

CREATE OR REPLACE FUNCTION create_schema_like(old_name name, new_name
name) RETURNS void AS $$
DECLARE
rel_name name;
old_schema text;
new_schema text;
ddl text;
path text;
BEGIN
path := current_setting('search_path');
old_schema := quote_ident(old_name);
new_schema := quote_ident(new_name);

EXECUTE 'CREATE SCHEMA '||new_schema;
FOR rel_name IN SELECT tablename FROM pg_tables WHERE
schemaname=old_schema LOOP
ddl := 'CREATE TABLE
'||new_schema||'.'||quote_ident(rel_name)

||' (LIKE '||old_schema||'.'||rel_name
||' INCLUDING DEFAULTS INCLUDING
CONSTRAINTS INCLUDING INDEXES)';
EXECUTE ddl;
END LOOP;
-- If we set search_path to old schema, definitions will have
schemanames from other schemas prepended where necessary
EXECUTE 'SET LOCAL search_path TO '||old_schema;
FOR rel_name, ddl IN SELECT viewname,definition FROM
pg_views WHERE schemaname = old_name LOOP
EXECUTE 'SET LOCAL search_path TO '||new_schema;
ddl := 'CREATE VIEW
'||quote_ident(rel_name)||' AS '||ddl;
EXECUTE ddl;
END LOOP;

EXECUTE 'SET LOCAL search_path TO '||path;
RETURN;
END;
$$ LANGUAGE PLpgSQL STRICT;

Of course you need also to:
* copy functions, types, etc, etc.
* pray that dependencies are met or get acquainted with pg_depend :)
* take care of ownerships, ACLs and tablespaces

In my opinion this is way too complicated to put it inside the backend.
It is mostly already inside pg_dump, so either pg_dump|sed|psql or
TODO: pg_dump: optional parameter for renaming schemas (and
tablespaces, and owners).

Regards,
Dawid
--
Solving [site load issues] with [more database replication] is a lot
like solving your own personal problems with heroin - at first it
sorta works, but after a while things just get out of hand.
- Fred B. Schneider, PhD