Re: Clone PostgreSQL schema

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 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%
>> 2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>
>>> [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%
>> 2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>
>>> [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%
>> 2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com
>>
>

*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.