Re: clone_schema function

Additional revision to avoid duplicating RI_Constraint triggers.

On Fri, Sep 18, 2015 at 4:09 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

> Revised to add rules after all tables are create to avoid error where
> table referenced in rule was not created yet.
>
> Added copying of column statistics with thanks to Marc Mamin for pointing
> that out.
>
>
> On Thu, Sep 17, 2015 at 12:06 PM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de> wrote:
>
>>
>> ------------------------------
>> *Von:* Melvin Davidson [melvin6925(at)gmail(dot)com]
>> *Gesendet:* Donnerstag, 17. September 2015 17:11
>> *An:* Marc Mamin
>> *Cc:* pgsql-general(at)postgresql(dot)org
>> *Betreff:* Re: [GENERAL] 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.
>>
>> might be that my modification is required when ( and only when ?) the
>> source_schema is not part of the current search_path.
>> This is just a guess, I only gave your code a quick try ...
>>
>> >As for SET STATISTICS, I'm guessing that is a glitch in the CREATE TABLE
>> .... LIKE option.
>> Yes, we can see it as an incomplete feature.
>>
>> regards,
>>
>> Marc Mamin
>>
>> 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.
>>
>
>
>
> --
> *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.