Re: How to copy a schema under another name in same database

Just to close the loop on my initial request, in the end we decided to go
with the approach of renaming the schema under the new name, dump it, rename
the schema back to its original name and do a restore of the dump, which will
recreate the schema under the new name.

Thanks to all for your suggestions, I will definitely keep the proposed
solution below in my notes!

---------------------------------
Daniel Gour
Adacel inc.

-----Original Message-----
From: Obe, Regina [mailto:robe(dot)dnd(at)cityofboston(dot)gov]
Sent: Wednesday, July 30, 2008 9:25 AM
To: Daniel Gour
Cc: pgsql-novice(at)postgresql(dot)org
Subject: RE: [NOVICE] How to copy a schema under another name in same
database

Daniel,

I had a typo in the below that I just noticed. Should be I think - but
I'm sure I screwed up in other ways, but hopefully you get the idea.

FOR tbltocopy IN(SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND
table_schema = param_source_schema ORDER BY table_name)

EXECUTE('CREATE TABLE ' || param_dest_schema || '.' || tbltocopy
|| '( LIKE ' || param_source_schema || '.' || tbltocopy || ' INCLUDING
DEFAULT CONSTRAINTS INDEXES );
INSERT INTO ' || param_dest_schema || '.' || tbltocopy || ' SELECT *
FROM ' param_source_schema || '.' || tbltocopy || ';');

If you need to copy views as well, that's a bit trickier since I assume
you would want your views to reference the specific schema objects
within the destination schema not the source schema.

For that take a look at information_schema.views -- it has a field
called view_definition.

You could use that to build the view and then macro replace the
references to the schema with the new schema I guess.

Something like
DECLARE vwdef information_schema.views --this part is iffy not sure if
a type is created for information_schema stuff

FOR vwdef IN(SELECT *
FROM information_schema.views
WHERE
table_schema = param_source_schema ORDER BY table_name)

EXECUTE('CREATE VIEW ' || param_dest_schema || '.' ||
vwdef.table_name
|| '( AS ' || REPLACE(vwdef.view_definition, param_source_schema || '.',
param_dest_schema || '.')
|| ';');

Hope that helps,
Regina

-----Original Message-----
From: Daniel Gour [mailto:Daniel(dot)Gour(at)adacel(dot)com]
Sent: Wednesday, July 30, 2008 9:12 AM
To: Obe, Regina
Cc: pgsql-novice(at)postgresql(dot)org
Subject: RE: [NOVICE] How to copy a schema under another name in same
database

Thanks for the information! That looks promising, I will attempt to
implement it this week and let you know.

---------------------------------
Daniel Gour
Adacel Inc.

-----Original Message-----
From: Obe, Regina [mailto:robe(dot)dnd(at)cityofboston(dot)gov]
Sent: Wednesday, July 30, 2008 7:14 AM
To: Daniel Gour; pgsql-novice(at)postgresql(dot)org
Subject: RE: [NOVICE] How to copy a schema under another name in same
database

Never had a need for this. One thought that comes to mind write a
plpgsql function that takes in name of new schema and old schema and
does something like below

--Create new tables in new schema

FOR tbltocopy IN(SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND
table_schema = param_source_schema ORDER BY table_name)

EXECUTE('CREATE TABLE ' || param_dest_schema || '.' || tbltocopy
|| '( LIKE ' || param_source_schema || '.' || tbltocopy || ' INCLUDING
DEFAULT CONSTRAINTS INDEXES ');
INSERT INTO ' || param_dest_schema || '.' || tbltocopy || ' SELECT *
FROM ' param_source_schema || '.' || tbltocopy || ';');

NEXT;

Hope that helps,
Regina

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org on behalf of Daniel Gour
Sent: Tue 7/29/2008 9:20 AM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] How to copy a schema under another name in same
database

Hello! I have a PostgreSQL (8.3.3, WinXP + linux) database with
multiple
schemas. I would like, on a regular basis, to be able to copy the
structure
and data of one schema under a new schema, using of course a different
name.
What would be the easiest way?

- I have seen the TODO entry "Add CREATE SCHEMA ... LIKE that
copies
a schema", so I guess an easy solution (a single pgsql command) is not
yet
available...

- I guess the alternative is to use pg_dump to dump a single
schema
and use pg_restore, but how can I restore the dumped information under
another schema? I would like to avoid dumping it as an sql script and
having
it to modify it manually, because this will be a regular operation that
I
would like to automate in my application.

Thanks in advance for any insight you can provide!

---------------------------------
Daniel Gour
Adacel Inc.

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.