Re: [GENERAL] clone_schema function

On Tue, Feb 14, 2017 at 7:46 AM, Michael Librodo wrote: > I had to modify the portion that copies FK constraint: > > https://gist.github.com/mlibrodo/6f246c483e650dc716eba752a9d3c79a > > Basically, the issue on my end was that the FK

Re: [GENERAL] clone_schema function

I had to modify the portion that copies FK constraint: https://gist.github.com/mlibrodo/6f246c483e650dc716eba752a9d3c79a Basically, the issue on my end was that the FK constraints on the cloned(destination) schema seem to reference the source_schema -- Sent via pgsql-general mailing list

Re: [GENERAL] clone_schema function

The script is generating foreign keys which reference tables in the source schema. I had to change 219SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname) 220 || ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' ' ||

Re: [GENERAL] clone_schema function

Good catch. Thank you very much! On Fri, Oct 23, 2015 at 10:50 AM, Predrag Maric wrote: > The script is generating foreign keys which reference tables in the source > schema. > I had to change > > 219SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || >

Re: [GENERAL] clone_schema function

ndet:* Donnerstag, 17. September 2015 17:11 >> *An:* Marc Mamin >> *Cc:* pgsql-general@postgresql.org >> *Betreff:* Re: [GENERAL] clone_schema function >> >> Thanks, >> >> >I'm not sure why you had trouble with the REPLACE(), as I did extensive

Re: [GENERAL] clone_schema function

wrote: > > -- > *Von:* Melvin Davidson [melvin6...@gmail.com] > *Gesendet:* Donnerstag, 17. September 2015 17:11 > *An:* Marc Mamin > *Cc:* pgsql-general@postgresql.org > *Betreff:* Re: [GENERAL] clone_schema function > > Thanks, > > >I'm not sure why yo

Re: [GENERAL] clone_schema function

Von: Melvin Davidson [melvin6...@gmail.com] Gesendet: Donnerstag, 17. September 2015 17:11 An: Marc Mamin Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] clone_schema function Thanks, >I'm not sure why you had trouble with the REPLACE(), as I

Re: [GENERAL] clone_schema function

Attached is hopefully the final version of FUNCTION clone_schema(text, text, boolean) This function now does the following: 1. Checks that the source schema exists and the destination does not. 2. Creates the destination schema 3. Copies all sequences, tables, indexes, rules, triggers,

Re: [GENERAL] clone_schema function

: [GENERAL] clone_schema function Attached is hopefully the final version of FUNCTION clone_schema(text, text, boolean) This function now does the following: 1. Checks that the source schema exists and the destination does not. 2. Creates the destination schema 3. Copies all sequences, tables, indexes

Re: [GENERAL] clone_schema function

> pgsql-general-ow...@postgresql.org] *On Behalf Of *Melvin Davidson > *Sent:* Donnerstag, 17. September 2015 15:48 > *To:* David G. Johnston > *Cc:* Igor Neyman; Jim Nasby; Daniel Verite; pgsql-general@postgresql.org > *Subject:* Re: [GENERAL] clone_schema function > > > > At

Re: [GENERAL] clone_schema function

On 9/14/15 8:02 PM, Melvin Davidson wrote: Actually, on further thought, you example shows that it works correctly because we do want all references to the old schema to be changed to the new schema, since all copies of functions will now reside in the new schema. Otherwise, there is no point of

Re: [GENERAL] clone_schema function

Igor, I understand your point, however, I have spent over a week making a function that previously did very little do a lot. Naming a table the same as a schema is a very silly idea. Unless you care to take the time to provide a full schema, and function that fails for reasonable , practical

Re: [GENERAL] clone_schema function

To make the casual user's life easier, in the face of this reality, it would nice if the routine would generate a reasonably attempted "diff" between the two so that all changes can be reviewed in a structured manner aided by correctly configured tools and advice. On Tue, Sep 15, 2015 at 12:20

Re: [GENERAL] clone_schema function

That is correct. But table old will NOT be converted to new because only the schema name is converted. And table "old" WILL exist because it will also be copied. I have tested and it works properly. Please do not provide hypothetical examples. Give me an actual working example that causes the

Re: [GENERAL] clone_schema function

I still do not see any problem. The whole purpose of the function is to copy ALL sequences , tables and functions to "new" schema, so new.old WILL exist. I don't see how you can possibly write a function that references a schema that does not yet exist! Again, please provide a _working_

Re: [GENERAL] clone_schema function

David, Yes, it would be nice, but 1. I am still working also on bringing over the comments for various objects 2. What you request is currently beyond my capability. Not to mention that there already are existing tools that do that, albeit they are not free. On Tue, Sep 15, 2015 at 12:27 PM,

Re: [GENERAL] clone_schema function

That is correct. But table old will NOT be converted to new because only the schema name is converted. And table "old" WILL exist because it will also be copied. I have tested and it works properly. Please do not provide hypothetical examples. Give me an actual working example that causes the

Re: [GENERAL] clone_schema function

I still do not see any problem. The whole purpose of the function is to copy ALL sequences , tables and functions to "new" schema, so new.old WILL exist. I don't see how you can possibly write a function that references a schema that does not yet exist! Again, please provide a _working_ example

Re: [GENERAL] clone_schema function

On 9/12/15 9:38 AM, Daniel Verite wrote: "seriously flawed" is a bit of a stretch. Most sane developers would not >have schema names of one letter. >They usually name a schema something practical, which totally avoids your >nit picky exception. That's confusing the example with the problem it

Re: [GENERAL] clone_schema function

Jim, Have you actually tried this, or is it just a theory? AFAIK, the function will work because only the schema name is changed.. So please provide a full working example of a function that fails and I will attempt a solution. On Mon, Sep 14, 2015 at 6:36 PM, Jim Nasby

Re: [GENERAL] clone_schema function

Actually, on further thought, you example shows that it works correctly because we do want all references to the old schema to be changed to the new schema, since all copies of functions will now reside in the new schema. Otherwise, there is no point of duplicating those functions. On Mon, Sep

Re: [GENERAL] clone_schema function

Melvin Davidson wrote: > "seriously flawed" is a bit of a stretch. Most sane developers would not > have schema names of one letter. > They usually name a schema something practical, which totally avoids your > nit picky exception. That's confusing the example with the problem it shows.

Re: [GENERAL] clone_schema function

With thanks to a suggestion by David G. Johnston, I've attached another revised version of the function that hopefully eliminates the problem reported by Daniel Verite. This version also handles CamelCase schemas and tables better. If anyone else finds a problem, kindly attach a dump of the

Re: [GENERAL] clone_schema function

Alvaro, no I haven't updated the wiki (or git). To be honest, I'm retired and I just don't want to bother learning something new, but I do enjoy helping othersfrom time to time. I would consider it a favor if you would do the update for me. TIA, Melvin On Fri, Sep 11, 2015 at 12:30 PM, Alvaro

Re: [GENERAL] clone_schema function

Thank you very much Alvaro. Now I can go back to being Chief Engineer of Sleeping Late @ retired. :) On Fri, Sep 11, 2015 at 2:21 PM, Alvaro Herrera wrote: > Melvin Davidson wrote: > > Alvaro, > > > > no I haven't updated the wiki (or git). To be honest, I'm retired

Re: [GENERAL] clone_schema function

Melvin Davidson wrote: > Here is one more tweak of clone_schema. Are you updating the wiki to match? If not (why?), I think at the very least you should add a link in the wiki page to this thread. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7

Re: [GENERAL] clone_schema function

Melvin Davidson wrote: > Alvaro, > > no I haven't updated the wiki (or git). To be honest, I'm retired and I > just don't want to bother learning something new, > but I do enjoy helping othersfrom time to time. I would consider it a favor > if you would do the update for me. I wouldn't want to

Re: [GENERAL] clone_schema function

"seriously flawed" is a bit of a stretch. Most sane developers would not have schema names of one letter. They usually name a schema something practical, which totally avoids your nit picky exception. However, if you are that concerned about the "serious flaw", you have the option of using the

Re: [GENERAL] clone_schema function

Melvin Davidson wrote: > Thank you very much Alvaro. Now I can go back to being Chief Engineer of > Sleeping Late @ retired. :) What? No! You still have a lot of other Snippet pages to go through to improve ;-) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL

Re: [GENERAL] clone_schema function

Melvin Davidson wrote: > I've added error checking and verified that it now copies the > current sequnce values, table data, views and functions. The code dealing with functions is seriously flawed. Consider that part: SELECT pg_get_functiondef(func_oid) INTO qry; SELECT

Re: [GENERAL] clone_schema function

Igor & David, You are correct, I am subject to criticism, However, I have spent a few days getting this to work as it should and it now does. Even though the chance of a collision is not zero, it is still low and the function does work. I don't mind criticism, but when someone finds a problem,

Re: [GENERAL] clone_schema function

On Fri, Sep 11, 2015 at 4:23 PM, Melvin Davidson wrote: > "seriously flawed" is a bit of a stretch. Most sane developers would not > have schema names of one letter. > They usually name a schema something practical, which totally avoids your > nit picky exception. >

Re: [GENERAL] clone_schema function

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Melvin Davidson Sent: Friday, September 11, 2015 4:24 PM To: Daniel Verite <dan...@manitou-mail.org> Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] clone_schema function "serio

Re: [GENERAL] clone_schema function

ptember 09, 2015 4:48 PM >> *To:* Igor Neyman <iney...@perceptron.com> >> *Cc:* pgsql-general@postgresql.org >> *Subject:* Re: [GENERAL] clone_schema function >> >> >> >> Thanks Igor, >> >> hmm, apparently the "INCLUDING CONSTRAINTS

Re: [GENERAL] clone_schema function

From: Melvin Davidson [mailto:melvin6...@gmail.com] Sent: Wednesday, September 09, 2015 4:48 PM To: Igor Neyman <iney...@perceptron.com> Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] clone_schema function Thanks Igor, hmm, apparently the "INCLUDING CONSTRAINTS' option of &q

Re: [GENERAL] clone_schema function

15 at 9:09 AM, Igor Neyman <iney...@perceptron.com> wrote: > > > > > *From:* Melvin Davidson [mailto:melvin6...@gmail.com] > *Sent:* Wednesday, September 09, 2015 4:48 PM > *To:* Igor Neyman <iney...@perceptron.com> > *Cc:* pgsql-general@postgresql.org &g

[GENERAL] clone_schema function

I noted there was an inquiry as to how to copy or clone_schema an entire schema. The standard method for doing that is to 1. pg_dump the schema in plain format 2. edit the dump file and change all occurrences of the schema name 3. reload the dump into the new schema. The attached function is an

Re: [GENERAL] clone_schema function

> *To:* pgsql-general@postgresql.org > *Subject:* [GENERAL] clone_schema function > > > > > I noted there was an inquiry as to how to copy or clone_schema > an entire schema. The standard method for doing that is to > 1. pg_dump the schema in plain format > 2. edit the

Re: [GENERAL] clone_schema function

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Melvin Davidson Sent: Wednesday, September 09, 2015 12:31 PM To: pgsql-general@postgresql.org Subject: [GENERAL] clone_schema function I noted there was an inquiry as to how to copy