Re: Feature request/suggestion - CREATE SCHEMA LIKE - Mailing list pgsql-general

On Mon, Mar 17, 2008 at 9:01 PM, wstrzalka <wstrzalka@gmail.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