Re: [GENERAL] Clone PostgreSQL schema

Поиск
Список
Период
Сортировка
От Melvin Davidson
Тема Re: [GENERAL] Clone PostgreSQL schema
Дата
Msg-id CANu8FiygzsBqHp1hbfKoNd1USTz4O4bFpf9+6h5K2FNBX_W0MQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [GENERAL] Clone PostgreSQL schema  (Melvin Davidson <melvin6925@gmail.com>)
Список pgsql-general


On Mon, Apr 17, 2017 at 11:02 AM, Melvin Davidson <melvin6925@gmail.com> wrote:


On Mon, Apr 17, 2017 at 9:42 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

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.


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.


Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Ron Ben
Дата:
Сообщение: Re: [GENERAL] Nice to have features: Percentage function
Следующее
От: Tom DalPozzo
Дата:
Сообщение: [GENERAL] tuple statistics update