Can't access data from pg_index using Foreign Data Wrapper in postgreSQL 9.3

Question

I implement Foreign data wrapper in postgreSQL 9.3 in another postgreSQL DATABASE as below:

CREATE SERVER app_db 
                   FOREIGN DATA WRAPPER postgres_fdw 
                   OPTIONS (dbname 'postgres', host 'localhost');

 CREATE USER MAPPING for postgres 
                      SERVER app_db 
                      OPTIONS (user 'postgres', password 'XXXX');



CREATE FOREIGN TABLE location_local
              (
                     id integer,
                    name character varying
               )
             SERVER app_db OPTIONS (table_name 'location') 

SELECT * FROM location_local;

This all works fine as location table is in public schema. but I also want to access data from pg_catalog. When I follow the same procedure to access the data than it gives me error.

ERROR:  relation "public.pg_catalog.pg_index" does not exist

is there any way to access data from catalog using FDW or any other way?

Please show the `CREATE FOREIGN TABLE` command that's related to the error you're getting. — Craig Ringer, Jul 29 '14 at 15:00

score 0 · Answer 1 · answered Jul 29 '14 at 15:05

You could try the schema_name option in the foreign table definition:

CREATE FOREIGN TABLE foreign_index (
    -- ...
)
SERVER app_db
OPTIONS (
    schema_name 'pg_catalog',
    table_name 'pg_index'
);

But that might won't work, because pg_catalog is not really a schema, but a system-catalog. If that is the case, you can still use the dblink module to run queries at a foreign database.

Can't access data from pg_index using Foreign Data Wrapper in postgreSQL 9.3

1 Answers1