Re: How to copy a schema under another name in same database
От | Obe, Regina |
---|---|
Тема | Re: How to copy a schema under another name in same database |
Дата | |
Msg-id | 53F9CF533E1AA14EA1F8C5C08ABC08D20479B888@ZDND.DND.boston.cob обсуждение исходный текст |
Ответ на | Re: How to copy a schema under another name in same database ("Daniel Gour" <Daniel.Gour@adacel.com>) |
Список | pgsql-novice |
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.Gour@adacel.com] Sent: Wednesday, July 30, 2008 9:12 AM To: Obe, Regina Cc: pgsql-novice@postgresql.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.dnd@cityofboston.gov] Sent: Wednesday, July 30, 2008 7:14 AM To: Daniel Gour; pgsql-novice@postgresql.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@postgresql.org on behalf of Daniel Gour Sent: Tue 7/29/2008 9:20 AM To: pgsql-novice@postgresql.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.
В списке pgsql-novice по дате отправления:
Предыдущее
От: "Daniel Gour"Дата:
Сообщение: Re: How to copy a schema under another name in same database
Следующее
От: Frank BaxДата:
Сообщение: Re: How to copy a schema under another name in same database