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

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

[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