Re: [GENERAL] Clone PostgreSQL schema - Mailing list pgsql-general

On Mon, Apr 17, 2017 at 2:20 AM, R. Reiterer <r.reiterer@posteo.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.reiterer@posteo.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.reiterer@posteo.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.reiterer@posteo.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

[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%2Bc-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%2Bc-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%2Bc-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.