Comments import

Currently PostgreSQL Foreign data wrapper API doesn't allow import comments from remote objects to foreign tables. Only names, nearest data types and some constraints can be imported automatically. Oracle comments usually is very usefully part of OracleDB and can be automatically imported to PostgreSQL.

Preconditions

You should create a sys schema in your PostgreSQL and import the following Oracle metadata table:

• sys.all_tab_comments
• sys.all_col_comments
• sys.all_tab_columns

Than you can execute a query with table comments import.

Import table comments from Oracle to PostgreSQL foreign tables

In SQL column of following query you will have COMMENT commands for PostgreSQL, other columns just gives full context. The query is suitable for all of foreign tables of current database, which is based on the same foreign server as previously imported sys.all_tab_comments foreign table.

-- Export comments for tables
with
topt as( -- 'table' option value, table name can be rewritten
select o.foreign_table_catalog,
       o.foreign_table_schema,
       o.foreign_table_name,
       o.option_value opt_table_name
  from information_schema.foreign_table_options o
 where o.option_name = 'table'
),
sopt as( -- 'schema' option value, schema name can be rewritten
select o.foreign_table_catalog,
       o.foreign_table_schema,
       o.foreign_table_name,
       o.option_value opt_schema_name       
  from information_schema.foreign_table_options o
 where o.option_name = 'schema'
),
pg_foreign_tables as ( -- PostgreSQL metadata for joining with Oracle metadata
select foreign_table_catalog,
       foreign_server_name,
       foreign_table_schema,
       foreign_table_name,       
       coalesce (t.opt_table_name, foreign_table_name) table_name,
       coalesce (s.opt_schema_name, foreign_table_schema) schema_name       
  from information_schema.foreign_tables ft     
  left join topt t
 using (foreign_table_catalog, foreign_table_schema, foreign_table_name)
  left join sopt s
 using (foreign_table_catalog, foreign_table_schema, foreign_table_name)
 )
select 'comment on foreign table "' || foreign_table_schema || '"."' || foreign_table_name || '" '
       || 'is ''' || replace(atc."comments", '''', '''''')  || ''';' "SQL",
       atc."owner" "ORA schema",
       atc.table_name "ORA table",
       atc."comments",
       foreign_table_catalog ft_db,
       foreign_table_schema ft_sc,
       foreign_table_name ft_tn
  from pg_foreign_tables ft 
  left join sys.all_tab_comments atc -- foreign table for Oracle table comments
    on ft.foreign_server_name = (select foreign_server_name
                                   from information_schema.foreign_tables ft2
                                  where ft2.foreign_table_schema ='sys'
                                    and foreign_table_name = 'all_tab_comments') 
   and atc."owner" = ft.schema_name
   and atc.table_name = ft.table_name
 where atc."comments" is not null
   and ft.foreign_table_catalog = current_catalog;

Import column comments from Oracle to PostgreSQL foreign tables

The query is suitable for all of foreign tables of current database, which is based on the same foreign server as previously imported sys.all_col_comments foreign table.

A helper view for PostgreSQL column metadata

create view sys.pg_foreign_column_metadata as (
with
topt as ( -- 'table' option value, table name can be rewritten
select o.foreign_table_catalog,
       o.foreign_table_schema,
       o.foreign_table_name,
       o.option_value opt_table_name
  from information_schema.foreign_table_options o
 where o.option_name = 'table'
),
sopt as ( -- 'schema' option value, schema name can be rewritten
select o.foreign_table_catalog,
       o.foreign_table_schema,
       o.foreign_table_name,
       o.option_value opt_schema_name       
  from information_schema.foreign_table_options o
 where o.option_name = 'schema'
),
pg_foreign_columns as ( -- PostgreSQL metadata for joining with Oracle metadata
select foreign_table_catalog,
       foreign_server_name,
       foreign_table_schema,
       foreign_table_name,
       c.column_name, 
       c.ordinal_position,
       coalesce (t.opt_table_name, foreign_table_name) table_name,
       coalesce (s.opt_schema_name, foreign_table_schema) schema_name       
  from information_schema."columns" c
 inner join information_schema.foreign_tables ft  
    on c.table_catalog = foreign_table_catalog
   and c.table_schema = foreign_table_schema
   and c.table_name = foreign_table_name 
  left join topt t
 using (foreign_table_catalog, foreign_table_schema, foreign_table_name)
  left join sopt s
 using (foreign_table_catalog, foreign_table_schema, foreign_table_name) 
 where foreign_table_catalog = current_catalog
)
select * from pg_foreign_columns
);

A helper view for Oracle column metadata

create view sys.oracle_column_comments as (
select acc."owner" schema_name,
       acc.table_name,
       acc.column_name,
       atc.column_id,
       acc."comments"
  from sys.all_col_comments acc -- foreign table for Oracle column comments
 inner join sys.all_tab_columns atc
    on acc."owner" = atc."owner" 
   and acc.table_name = atc.table_name 
   and acc.column_name = atc.column_name
 where acc."comments" is not null
 );

A query for table columns comments command

In SQL column of following query you will have COMMENT commands for PostgreSQL, other columns just gives full context.

select 'comment on column "' || foreign_table_schema || '"."' || foreign_table_name || '"."' || pgfc.column_name || '" '
       || 'is ''' || replace(ora_md."comments", '''', '''''')  || ''';' "SQL",
       ora_md.schema_name "ORA schema",
       ora_md.table_name "ORA table",
       ora_md.column_name "ORA column",
       ora_md."comments",
       '"' || foreign_table_schema || '"."' || foreign_table_name || '"."' || pgfc.column_name  || '"' pg_obj,
       ora_md.column_id,
       pgfc.ordinal_position
  from sys.pg_foreign_column_metadata pgfc
 inner join sys.oracle_column_comments ora_md
    on ora_md.schema_name = pgfc.schema_name
   and ora_md.table_name = pgfc.table_name 
   and ora_md.column_id = pgfc.ordinal_position