Thanks, I'm not sure why you had trouble with the REPLACE(), as I did extensive testing and it was working as coded.
As for SET STATISTICS, I'm guessing that is a glitch in the CREATE TABLE .... LIKE option. On Thu, Sep 17, 2015 at 11:05 AM, Marc Mamin <m.ma...@intershop.de> wrote: > Hello, > > I had to make 2 changes to get it running: > > > > line 193: > > - REPLACE(column_default::text, quote_ident(source_schema) || '.', > quote_ident(dest_schema || '.') ) > > + REPLACE(column_default::text, quote_ident(source_schema) || '.', > quote_ident(dest_schema) || '.' ) > > > > line 319 > > - SELECT replace(v_def, ' ON ', ' ON ' || quote_ident(dest_schema) || > '.') INTO dest_qry; > > + SELECT replace(v_def, ' ON ' || quote_ident(source_schema), ' ON ' || > quote_ident(dest_schema) ) INTO dest_qry; > > > > > > moreover, you don't take care of the column statistic targets > > (i.e. ALTER TABLE t ALTER COLUMN c SET STATISTICS n;) > > > > > > regards, > > > > Marc Mamin > > > > > > *From:* pgsql-general-ow...@postgresql.org [mailto: > 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 > > > > 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, > data(optional), > views & functions from the source schema to the destination schema > 4. Optionally copies records from source schema tables to destination > tabled. (boolean) > 5. Copies comments for source schema and all sequences, tables, > functions, rules and triggers; > > If you discover a problem with this function, then kindly advise me what > it is > and attach a script (SQL dump) to duplicate it. If you also have a fix, > that is > even better. > > However, if you "think" there is a problem that occurs when > A. The moon is full > B. You have blood type A/B negative > C. You have a table the same name as your database and schema > D. All you tables have column "id" in them > E. You've had 16 beers and 4 oxycodones > F. Your meth supplier raised the price > > then do not contact me. Instead, run, do not walk, immediately to your > psychologist, as you have serious issues in addition to database design > problems > and you should not use this function under any circumstance. > > CAVEAT EMPTOR! > The only known problem with this script is if functions in the source > schema > have a SELECT using the form of tablename.columm, and tablename is the > same > as source schema, then tablename will be changed to destination schema > name. > However, since good developers and DBA's use the form of alias.column, > this > should rarely be a problem. > > > > On Tue, Sep 15, 2015 at 12:37 PM, Melvin Davidson <melvin6...@gmail.com> > wrote: > > 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, David G. Johnston < > david.g.johns...@gmail.com> wrote: > > 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 PM, Melvin Davidson <melvin6...@gmail.com> > wrote: > > 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 design > > I will ignore all further comments. > > > > On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman <iney...@perceptron.com> > wrote: > > > > 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 problem. > > This statement: > > SELECT old.field FROM old.old; > > selects column “field” from table “old” which is in schema “old”. > > Your script converts it into: > > SELECT new.field FROM new.old > > which will try to select column “field” from table “old” in schema “new”. > > > > Again: > > SELECT new.field > > means select column “field” from table “new”, which does not exists. > > Not sure, what other example you need. > > Regards, > > Igor Neyman > > > > > -- > > *Melvin Davidson* > > *I reserve the right to fantasize. Whether or not you wish to share my > fantasy is entirely up to you. * > > > > > > > -- > > *Melvin Davidson* > > *I reserve the right to fantasize. Whether or not you wish to share my > fantasy is entirely up to you. * > > > > > -- > > *Melvin Davidson* > > *I reserve the right to fantasize. Whether or not you wish to share my > fantasy is entirely up to you. * > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
