Question. oracle_fdw - Import foreign schema limit to synonym?

Question

oracle_fdw - ‎Is it possible in PostgreSQL to import foreign schema with a limit to synonym - as in the attached image? ㅤ

For example, this not working (limit to synonym):

IMPORT FOREIGN SCHEMA "UserA" LIMIT TO (A_SYNONYM) FROM SERVER oracledb INTO public;

but works when I try to import foreign schemat limit to view or table:

IMPORT FOREIGN SCHEMA "UserB" LIMIT TO (B_VIEW) FROM SERVER oracledb INTO public;

‎ So, it is possible to use synonyms in import foreign schema "LIMIT TO ()"?

‎

Answer 1

When processing IMPORT FOREIGN SCHEMA, oracle_fdw searches through the Oracle view ALL_TAB_COLUMNS in Oracle. According to the Oracle documentation

ALL_TAB_COLUMNS describes the columns of the tables, views, and clusters accessible to the current user.

That sounds like synonyms won't be listed. You can easily test that by omitting the LIMIT TO clause. If foreign tables for synonyms are not created, you have a proof.

As a workaround, you could import those tables from the schemas where they really reside.