Is it possible to create a foreign table, using Postgres Foreign Data Wrapper, that points to a view instead of a table?
Add a comment
|
3 Answers
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.
2 Comments
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');
Comments
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.
