Re: Clone PostgreSQL schema

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.