Re: Cloning schemas - Mailing list pgsql-general

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@aklaver.com