Re: Clone PostgreSQL schema

On Mon, Apr 17, 2017 at 11:02 AM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

>
>
> On Mon, Apr 17, 2017 at 9:42 AM, Melvin Davidson <melvin6925(at)gmail(dot)com>
> wrote:
>
>>
>> On Mon, Apr 17, 2017 at 2:20 AM, R. Reiterer <r(dot)reiterer(at)posteo(dot)at>
>> wrote:
>>
>>> Hi Melvin,
>>>
>>> thanks again for your help! I did some testing, but views in the new
>>> schema still refer to the old schema.
>>>
>>> Regards, Reinhard
>>>
>>> Am 17.04.2017 04:53 schrieb Melvin Davidson:
>>>
>>>> On Sun, Apr 16, 2017 at 4:42 PM, R. Reiterer <r(dot)reiterer(at)posteo(dot)at>
>>>> wrote:
>>>>
>>>> Unfortunately, I do not have the skills to improve the function.
>>>>> Maybe someone at dba.stackexchange.com [1] can help me. I'll open a
>>>>>
>>>>> ticket. I hope this is okay for you.
>>>>>
>>>>> Am 16.04.2017 22:31 schrieb Melvin Davidson:
>>>>> I missed to note that this is a VIEW issue (?)
>>>>> AH, IN THAT CASE, YOU ARE RIGHT. I AM RETIRED, BUT IF YOU WISH, GO
>>>>> AHEAD AND APPLY THE FIX YOURSELF. JUST MAKE A COMMENT AT THE TOP TO
>>>>> INCLUDE THE DATE, YOUR NAME AND THE FIX.
>>>>>
>>>>> On Sun, Apr 16, 2017 at 4:24 PM, R. Reiterer <r(dot)reiterer(at)posteo(dot)at>
>>>>> wrote:
>>>>>
>>>>> Hi Melvin,
>>>>>
>>>>> thanks for your reply. I missed to note that this is a VIEW issue
>>>>> (?). After duplicating a schema, views in the cloned schema
>>>>> (schema_new) refer still to the source schema (schema_old) in the
>>>>> FROM clause:
>>>>>
>>>>> View in cloned schema (schema_new) -->
>>>>>
>>>>> CREATE VIEW schema_new.my_view AS
>>>>> SELECT *
>>>>> FROM schema_old.my_table;
>>>>>
>>>>> To me 'FROM schema_new.my_table' would be more logical.
>>>>>
>>>>> Regards, Reinhard
>>>>>
>>>>> Am 16.04.2017 22:12 schrieb Melvin Davidson:
>>>>> On Sun, Apr 16, 2017 at 3:20 PM, R. Reiterer <r(dot)reiterer(at)posteo(dot)at>
>>>>> wrote:
>>>>>
>>>>> Hi Melvin,
>>>>>
>>>>> I use your PL/pgSQL function posted at
>>>>>
>>>>>
>>>>> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2
>>>> Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>>>
>>>>> [2]
>>>>>
>>>>> [1]
>>>>> [1] to clone schemas in PostgreSQL databases. Many thanks for your
>>>>> work!
>>>>>
>>>>> I noticed that in cloned schemas the schema name isn't updated in
>>>>> the FROM clause:
>>>>>
>>>>> schema_old -->
>>>>>
>>>>> CREATE VIEW schema_old.my_view AS
>>>>> SELECT *
>>>>> FROM schema_old.my_table;
>>>>>
>>>>> schema_new -->
>>>>>
>>>>> CREATE VIEW schema_new.my_view AS
>>>>> SELECT *
>>>>> FROM schema_old.my_table;
>>>>>
>>>>> Are you interessted to fix this?
>>>>>
>>>>> Regards,
>>>>>
>>>>> Reinhard
>>>>>
>>>>> FIRST, THANK YOU FOR THE COMPLEMENT.
>>>>>
>>>>> However, AFAIC, there is nothing to "fix" with regards to cloning
>>>>> schema name. In a database, you cannot have two schemas with the
>>>>> same
>>>>> name,
>>>>>
>>>>> so what would be the point? If you want to "clone" to a different
>>>>> database, then just use pg_dump and pg_restore.
>>>>>
>>>>> --
>>>>>
>>>>> MELVIN DAVIDSON
>>>>> I reserve the right to fantasize. Whether or not you
>>>>> wish to share my fantasy is entirely up to you.
>>>>>
>>>>> Links:
>>>>> ------
>>>>> [1]
>>>>>
>>>>>
>>>>> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2
>>>> Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>>>
>>>>> [2]
>>>>> [1]
>>>>>
>>>>> --
>>>>>
>>>>> MELVIN DAVIDSON
>>>>> I reserve the right to fantasize. Whether or not you
>>>>> wish to share my fantasy is entirely up to you.
>>>>>
>>>>> Links:
>>>>> ------
>>>>> [1]
>>>>>
>>>>> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2
>>>> Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>>>
>>>>> [2]
>>>>>
>>>>
>>>> Reinhard,
>>>>
>>>> After reviewing things, I note it's possible that you downloaded an
>>>> earlier version that had some errors in it and was not as complete.
>>>>
>>>> Therefore, I've attached the latest, more complete version of the
>>>> function. Please let me know if this solves the problem.
>>>>
>>>> --
>>>>
>>>> MELVIN DAVIDSON
>>>> I reserve the right to fantasize. Whether or not you
>>>> wish to share my fantasy is entirely up to you.
>>>>
>>>>
>>>> Links:
>>>> ------
>>>> [1] http://dba.stackexchange.com
>>>> [2]
>>>> https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2
>>>> Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>>>
>>>
>>
>>
>> *My apologies,*
>>
>> *I though I had had a fix. I even worked on it a couple of hours this
>> morning, but it seems it's a bit trickier than I thought. I'll keep trying*
>>
>> *until I get it right.*
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize. Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
> *OK Reinhard, I think I have it, please try the revision I have attached.*
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>

*Oops, I forgot to remove the premature RETURN, use this latest attached*.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.