1

I know that dblink can be queried directly like:

select * from dblink('kenyon_dblink','select * from test') as t1 (id integer,name varchar);

I wonder if I can query foreign servers without creating foreign tables, since my query strings are generated dynamically.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
xiagao1982
  • 1,077
  • 1
  • 13
  • 25

1 Answers1

0

Yes you can, just open a dblink connection in the same session with dblink_connect() (named or unnamed).

Example:

Example with dynamic SQL:

This works for connections between PostgreSQL DBs, not for other RDBMS. Per documentation:

dblink is a module that supports connections to other PostgreSQL databases.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I created a foreign data wrapper and a foreign server to MS sql server. Can I query this foreign server directly without creating foreign tables? – xiagao1982 Jul 26 '15 at 14:11
  • I created a foreign data wrapper and a foreign server like this: `create extension tds_fdw; create server mssql_svr foreign data wrapper tds_fdw options (servername '127.0.0.1', port '1433', database 'testdb', tds_version '7.1'); create user mapping for postgres server mssql_svr options (username 'user', password 'passwd');` Could you please give me an example how to query mssql_svr? Thanks! – xiagao1982 Jul 26 '15 at 14:31
  • @xiagao1982: Oh sorry, I missed that you just mentioned MS SQL Server in your comment (which must be in the question, really). [Per documentation](http://www.postgresql.org/docs/current/interactive/dblink.html)`: dblink is a module that supports connections to other PostgreSQL databases`. – Erwin Brandstetter Jul 26 '15 at 15:16
  • So, you are saying that there is no way to query a table in sqlserver or oracle database without creating a foreign table? Really? They must gotta be kidding us! – Christian Oct 07 '15 at 13:51
  • @ChristianB.Almeida: How would Postgres know how to access foreign data? Would you know of *any* RDBMS that can access foreign tables in different RDBMS automatically? (I don't.) Consider starting a new *question* instead of discussing it in the comments here. – Erwin Brandstetter Oct 07 '15 at 14:05
  • Look... I don't want to compare apples with oranges, but Oracle "database link" does it very well, whoever remote database is. What I am trying to say is that "fdw" is very burocratic and doesnt make any sense. I know it is not postgres developers fault... but it will be easier if we can just do: "select * from table@remotedatabase", and don't worry about anything else! – Christian Oct 07 '15 at 14:49
  • Sure would be a lot easier, but that seems like a hell of a task. Not sure major (proprietary) RDBMS would even be *willing* to cooperate. – Erwin Brandstetter Oct 07 '15 at 14:54