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.klaver@xxxxxxxxxxx
