Oracle's user_external_locations equivalent in Postgres

Question

In postgres, is there a system table that stores the mapping between foreign (external) table and its corresponding file on the disk. Similar to location and table_name mapping in user_external_locations table in oracle.

I have used foreign file wrapper - file_fdw to create the external table. I looked at pg_foreign_tables but that does not have the information I seek.

create extension file_fdw;

create server oem_dat_dir foreign data wrapper file_fdw;

CREATE FOREIGN TABLE trial.xtab_vehicle ( vehicle_syskey int8 NULL, vehicle_line_syskey int NULL ) SERVER oem_dat_dir OPTIONS (filename 'c:\dat\vehicle.csv', format 'csv', delimiter '|');

When i read file c:\dat\vehicle.csv, I want to know the external table it corresponds to i.e. trial.xtab_vehicle.

score 1 · Accepted Answer · answered Sep 13 '19 at 19:34

with the following query you can get table->filename for file_fdw in PostgreSQL

select * from 
(
 select relname,unnest(ftoptions) opt from pg_foreign_table  join 
 pg_foreign_server on (pg_foreign_table.ftserver=pg_foreign_server.oid)
 join pg_foreign_data_wrapper on  
 (pg_foreign_server.srvfdw=pg_foreign_data_wrapper.oid) 
 join pg_class on (pg_foreign_table.ftrelid=pg_class.oid)
  where  fdwname= 'file_fdw'
  ) as dat 
  where opt like 'filename%'

Perfect. This is what I was looking for. Thanks. – Sarika Sep 14 '19 at 20:42 — Sarika, Sep 14 '19 at 20:42

Oracle's user_external_locations equivalent in Postgres

1 Answers1