Is there any shortcut for using dblink in Postgres?

Question

In Postgres, you can link to your other databases using dblink, but the syntax is very verbose. For example you can do:

SELECT *
FROM dblink (
    'dbname=name port=1234 host=host user=user password=password',
    'select * from table'
) AS users([insert each column name and its type here]);

Is there any way to do this faster? Maybe pre-define the connections?

I noticed that Postgres has a new create foreign table function for connecting to a MySQL database. It has a simpler syntax than dblink. Could I use that?

Hydrolyte

Answer 1

In PostgreSQL 8.4 and later, you can use the foreign data wrapper functionality to define the connection parameters. Then you'd simply refer to the foreign server name in your dblink commands. See the example in the documentation.

In PostgreSQL 9.1 and later, you can use the foreign data wrapper functionality to define foreign tables and thus access remote databases transparently, without using dblink at all. For that, you'd need to get the postgresql_fdw wrapper, which isn't included in any production release yet, but you can find experimental code in the internet. In practice, this route is more of a future option.

Scaly

Nowadays known as postgres_fdw and included in standard PostgreSQL. – Scaly

Answer 2

You can wrap connection parameters in a FOREIGN SERVER object like @Peter explains, but you'd still have to spell out the rest.

You can encapsulate everything in a view or function, so you type it only once. Example with a function - Run as superuser:

CREATE OR REPLACE FUNCTION f_lnk_tbl()
  RETURNS TABLE(tbl_id int, col1 text, log_ts timestamp) AS
$BODY$

SELECT *
  FROM dblink(
  'SELECT tbl_id, col1, log_ts
   FROM   tbl
   ORDER  BY tbl_id'::text) AS b(
 tbl_id int
,col1   text
,log_ts timestamp);

$BODY$ LANGUAGE sql STABLE SECURITY DEFINER;
REVOKE ALL ON FUNCTION f_lnk_tbl() FROM public;


CREATE OR REPLACE FUNCTION f_sync()
  RETURNS text AS
$BODY$

SELECT dblink_connect('hostaddr=123.123.123.123 port=5432 dbname=mydb
                       user=postgres password=*secret*');

INSERT INTO my_local_tbl SELECT * FROM f_lnk_tbl();
-- more tables?

SELECT dblink_disconnect();

$BODY$
  LANGUAGE sql VOLATILE SECURITY DEFINER;
REVOKE ALL ON FUNCTION blob.f_dbsync() FROM public;
-- GRANT ....;

Commutual

Well, that isn't what I wanted to hear, but I guess that's the answer. Thanks, – Hydrolyte

Sorry, the guy gave me the answer I really wanted, but that's still good info. – Hydrolyte

@Joe: Well, Peter is a postgres developer, his info should be good! ;) I am looking forward to FDW functionality, too. – Commutual