Обсуждение: Feature request/suggestion - CREATE SCHEMA LIKE
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 ?
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. +