current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite
1

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/

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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:

share|improve this answer
1  
The example you provided is for 'file_server'. Can you edit the example to connect to an external postgresql server? Thanks. –  archmeta Jul 17 '11 at 7:27
    
It'll then be the same kind of syntax with a different server. See the relevant docs here: postgresql.org/docs/current/static/sql-createserver.html / CREATE SERVER myserver FOREIGN DATA WRAPPER pgsql OPTIONS (host 'foo', dbname 'foodb', port '5432'); –  Denis Jul 17 '11 at 7:56
    
I get the error "ERROR: foreign-data wrapper "pgsql" does not exist" on my 9.1 - Any ideas what I am missing? Thanks –  alfonx Feb 12 '13 at 22:02
up vote 1 down vote

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...

share|improve this answer
up vote 0 down vote

If you want to join 2 different postgresql databases I recommend you to use dblink:

select datos.* 
     FROM dblink('hostaddr=192.168.0.10 port=5432 dbname=my_dbname user=my_user password=my_pass'::text, ' 
               select field_1, field_2 
               from my_table  order by field_1
               '
               ::text) 
      datos(field_1, integer, field_2 character varying(10));

(I tested it on PostgreSQL 9.1.3) http://www.postgresql.org/docs/9.2/static/contrib-dblink-function.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.