How to use PostgreSQL Foreign Data Wrapper to join 2 different postgresql databases

Question

Can anyone provide an example (with the various SQL statements involved) on how to use foreign data wrappers in postgresql to enable a table from a postgresql database A to be joined to a table from a postgresql database B?

It is unclear from the docs to what degree is the FDW functionality available in pgsql 9.0 versus 9.1. The docs also do not have any examples that shows how to join between 2 different postgresql databases (with WHERE qualifier push-down) using FDW.

http://www.postgresql.org/docs/9.0/static/sql-createforeigndatawrapper.html

http://www.postgresql.org/docs/9.1/static/ddl-foreign-data.html

http://www.depesz.com/index.php/2011/03/14/waiting-for-9-1-foreign-data-wrapper/

Answer 1

You manipulate it just like any table. Per Depesz' post:

CREATE FOREIGN TABLE passwd (
    username text,
    pass text,
    uid int4,
    gid int4,
    gecos text,
    home text,
    shell text
) SERVER file_server
OPTIONS (format 'text', filename '/etc/passwd', delimiter ':', null '');

select * from passwd;

The docs have no examples of joining tables for a good reason: it's plain old SQL...

The join push-down currently is the subject of a GSOC:

• http://wiki.postgresql.org/wiki/Enhancing_FDW_functionality_for_PostgreSQL_GSoC2011
• http://nine-chapters.com/?p=5

Answer 2

The simplest solution I found is the dblink extension. I tested it on PostgreSQL 9.1:

create extension dblink.
select * from dblink('port=5452 host=localhost dbname=mydb user=myuser password=xxx', 
                     'select id,spaltenname from variablen') as v (a int, b varchar(20));

http://www.postgresql.org/docs/9.1/static/dblink.html

A simple join would be:

with a as (select * from dblink('port=5452 host=localhost dbname=mydb user=myuser password=xxx', 'select id,spaltenname from variablen') as v (a int, b varchar(20)))
select a join (select 1)b on (true);

The example above enables you to join with a table on another postgresql server, but it is just a copy and then join. No automatic "WHERE qualifier push-down" as you called it. You could of course just select the lines WHERE you need them in the first statement...