By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,691 Members | 2,041 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,691 IT Pros & Developers. It's quick & easy.

Creating schema copy

P: n/a
Hi,

In a database I have to create new schemas with exactely the same structure as
the default one. Of course, I could reverse a schema with pg_dump, then apply
the script to the newly created one. The problem is the base schema sometimes
changes so I should generate scripts every time. Also I need to do this from
a stored function.

My question: is it possible to extend (in near future) CREATE SCHEMA syntax
with feature like:
CREATE SCHEMA user_xxx_schema FROM default_user_schema [WITH CONTENT];
?
It should create *every* object existing in 'default_user_schema' in
'user_xxx_schema' preserving of course names, constraints, triggers, etc.
Optionally it could also copy table contents (but this is not very
important).

Or is there any other (relatively simple and safe) way do do this *inside* a
stored function (pl/pgsql)?

Regards,

Mariusz

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Mariusz =?iso-8859-2?q?Czu=B3ada?= <ma****@idea.net.pl> writes:
My question: is it possible to extend (in near future) CREATE SCHEMA syntax
with feature like:
CREATE SCHEMA user_xxx_schema FROM default_user_schema [WITH CONTENT];
?
It should create *every* object existing in 'default_user_schema' in
'user_xxx_schema' preserving of course names, constraints, triggers, etc.
Optionally it could also copy table contents (but this is not very
important).


AFAICS this would require a code body approximately as large,
complicated, and maintenance-needy as pg_dump itself; and being inside
the backend, it could share little or no physical code with pg_dump.

So no, it's not very likely to happen in the near future. pg_dump is
your best bet.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.