Re: clone_schema function

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(dot)Mamin(at)intershop(dot)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-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)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(at)postgresql(dot)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 <melvin6925(at)gmail(dot)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(dot)g(dot)johnston(at)gmail(dot)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 <melvin6925(at)gmail(dot)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 <ineyman(at)perceptron(dot)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.