Select * From table As a table dblink

Question

To select from another database I try to use dblink or fdw extension of Postgres, like this:

CREATE EXTENSION dblink;

SELECT * FROM 
dblink ('dbname = bd_name port = 5432 host = 10.6.6.6 user = username password = password', 
'SELECT id, code FROM sch_schema.table') 
AS new_table(id INTEGER, code character varying);

This works fine when I specify which columns I want to select.

My problem is: How can I select all the columns?

I tried this:

SELECT * FROM 
dblink ('dbname = bd_name port = 5432 host = 10.6.6.6 user = username password = password', 
'SELECT * FROM sch_schema.table');

But this does not work. How can I solve this problem?

Oh Really ?. Anyone with adequate reputation could close a question that doesn't mean that I did it. — Vivek S., Jan 30 '17 at 13:27
With FDWs, there is an [`IMPORT FOREIGN SCHEMA`](https://www.postgresql.org/docs/current/static/sql-importforeignschema.html) command from PostgreSQL 9.5+, but nothing similar in `dblink`. — pozs, Jan 30 '17 at 14:05

score 3 · Accepted Answer · answered Jan 30 '17 at 15:41

3

You cannot do that, because PostgreSQL must know at query planning time what the columns will be.

But it shouldn't be a problem to specify the columns, and anyway, in most cases it is good practice to avoid the “*” in SQL.

answered Jan 30 '17 at 15:41

Laurenz Albe

209,280
17
206
263

score 2 · Answer 2 · answered Dec 04 '17 at 05:32

DBLink returns a generic data row type. One of the annoying things about this is that in order to use it, you need to specify the output structure. Perhaps in future versions of PostgreSQL, this limitation of lack of introspection of generic row types will be irradicated. The lack of introspection is an issue not only for DbLink, but for other generic row type returning functions such as TableFunc cross tab functions

Select * From table As a table dblink

2 Answers2