Re: Cloning schemas

On 07/09/2018 02:50 PM, Melvin Davidson wrote:
>
> Adrian,
> The code that CREATES the TABLE is
>
> EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' ||
> quote_ident(source_schema) || '.' || quote_ident(object)
>         || ' INCLUDING ALL)';
>
> The schema names are supposed to be changed!
>
> This function HAS been tested and does WORK. Please do not muddle the
> problem without testing yourself.
>

create table public.idx_test (id int, fld_1 varchar);
create index test_idx on idx_test (id);

test_(postgres)# \d idx_test
Table "public.idx_test"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | |
fld_1 | character varying | | |
Indexes:
"test_idx" btree (id)

create table sch_test.idx_test (like public.idx_test including all);

test_(postgres)# \d sch_test.idx_test
Table "sch_test.idx_test"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
id | integer | | |
fld_1 | character varying | | |
Indexes:
"idx_test_id_idx" btree (id)

When you look up the comments you do:

SELECT oid
FROM pg_class
WHERE relkind = 'i'
AND relnamespace = src_oid

Where src_oid is the source namespace/schema. So in this case:

test_(postgres)# SELECT oid, relname
FROM pg_class
WHERE relkind = 'i'
AND relnamespace = 'public'::regnamespace AND oid=2089851;
oid | relname
---------+----------
2089851 | test_idx

You then do:

SELECT relname INTO object ..

EXECUTE 'COMMENT ON INDEX ' || quote_ident(dest_schema) || '.' ||
quote_ident(object)
|| ' IS ''' || v_def || ''';';

The problem is that the relname/object has changed in the new schema. In
this case from text_idx --> idx_test_id_idx. So this happens:

test_(postgres)# comment on index sch_test.test_idx is 'test';
ERROR: relation "sch_test.test_idx" does not exist

Just some muddling do with it what you will:)

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com