Feature request/suggestion - CREATE SCHEMA LIKE

wstrzalka 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 believe the CREATE DATABASE was because of the way postgresql creates
a database. I thought it just copied the template database (but are not
completely sure). I also believe CREATE TABLE LIKE was mostly for
temporary tables, where the query can not be sure what the table
actually looks like when it is invoked.

Would it not be possible to work around the SCHEMA LIKE by just dumping
the database schema and restoring it to a new schema? This seems more
like a part that should be under strict user control and not automated
by common queries.

- Joris

Joris Dobbelsteen wrote:

wstrzalka 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 believe the CREATE DATABASE was because of the way postgresql creates
a database. I thought it just copied the template database (but are not
completely sure). I also believe CREATE TABLE LIKE was mostly for
temporary tables, where the query can not be sure what the table
actually looks like when it is invoked.

Would it not be possible to work around the SCHEMA LIKE by just dumping
the database schema and restoring it to a new schema? This seems more
like a part that should be under strict user control and not automated
by common queries.

No one has actually asked for CREATE SCHEMA LIKE before but we could add
it to the TODO list if we can find a few people who want the feature.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

On Mar 17, 4:01 pm, wstrzalka <wstrza...@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 ?

Hey there,

I have been using scripts for a while, but it would be definitely
helpful and very nice to have those features. It would make my life
much easier.
If it s possible, please do it!
Thanks.

On Mar 18, 2008, at 7:17 AM, Joris Dobbelsteen wrote:

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 believe the CREATE DATABASE was because of the way postgresql
creates a database. I thought it just copied the template database
(but are not completely sure).

You are correct. This is why the database used as a template can't
have anyone connected to it; if somebody was, we can't get a
consistent filesystem-level copy of the database.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Added to TODO:

o Add CREATE SCHEMA ... LIKE that copies a schema

---------------------------------------------------------------------------

ipajor@gmail.com wrote:

On Mar 17, 4:01 pm, wstrzalka <wstrza...@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 ?

Hey there,

I have been using scripts for a while, but it would be definitely
helpful and very nice to have those features. It would make my life
much easier.
If it s possible, please do it!
Thanks.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +