Postgres: Oracle_fdw: Import foreign schema does not support if not exists

Question

When we use import foreign schema in oracle_fdw, there is no option for IF NOT EXISTS. Due to which if we re-execute the import foreign schema command to import the newly added tables/view we get the error that relation already exists.

As we are not aware of the table/view which were not added in the previous execution it becomes difficult to specify them in LIMIT/EXCEPT clause

Is there any work around available to achieve the IF NOT EXISTS functionality

Answer 1

There is no direct way to do that, but you can first find the foreign tables that already exist in the target schema and list them in the EXCEPT clause of IMPORT FOREIGN SCHEMA.

To find all foreign tables in a schema:

SELECT relname
FROM pg_class
WHERE relkind = 'f'
  AND relnamespace = 'schemaname'::regnamespace;

Then

IMPORT FOREIGN SCHEMA "XYZ"
EXCEPT (/* list from above */)
FROM SERVER oraserver
INTO schemaname;