Create a foreign table pointing to a view in Postgres

Question

Is it possible to create a foreign table, using Postgres Foreign Data Wrapper, that points to a view instead of a table?

score 17 · Accepted Answer · answered May 16 '14 at 15:07

17

Yes, it is possible!

The following query worked perfectly:

CREATE FOREIGN TABLE facts(name character varying(255))
SERVER my_server 
OPTIONS (table_name 'facts');

Where facts is a view in my_server instead of a table.

answered May 16 '14 at 15:07

Nícolas Iensen

3,899
4
22
26

2

AFAIK you don't have to specify the `table_name` option if it is the same as the foreign table's name. – fphilipe Jan 13 '15 at 08:23
1

You don't have to specify the table definition either if you use `IMPORT FOREIGN SCHEMA`, but you do have to explicitly include the view in the `LIMIT TO` clause. – OrangeDog May 15 '19 at 13:06

score 9 · Answer 2 · answered Feb 19 '18 at 09:32

Recently I had to do the same thing and here are the steps that worked for me. All these commands are run on the local postgreSQL DB.

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server_name
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '10.10.10.10', port '5432', dbname 'remote_db_name');

CREATE USER MAPPING FOR local_user_name
  SERVER remote_server_name
  OPTIONS (user 'remote_user', password 'remote_password');

CREATE FOREIGN TABLE local_table_name (
  id NUMERIC NOT NULL,
  row TEXT,
  another_row INTEGER,
  whatever_row TEXT
)
  SERVER remote_server_name
  OPTIONS (schema_name 'public', table_name 'remote_table_name');

score 1 · Answer 3 · answered Jun 22 '20 at 06:34

I have the same question. In pgadmin4 for postgresql-11, if use GUI Command: Create -> Foreign Table... on table, it works; but on view, it does't works, you will get a empty table.

for view, i use this code, it works:

IMPORT FOREIGN SCHEMA remote_schema_name
LIMIT TO (remote_view_name)
FROM SERVER remote_host_map_name INTO local_shema_name;

The reason is, for table, pgadmin4 can create columns same as remote table in constract SQL statement, but for view, it create no columns in constract SQL statement.

Create a foreign table pointing to a view in Postgres

3 Answers3