How to create Postgres to Postgres foreign data table

Question

I need to access tables in a remote Postgres database. I spent a couple hours reading through docs last night, and this is what I've come up with.

-- surely this thing is already written somewhere?
CREATE FUNCTION pgsql_fdw_handler()
    RETURNS fdw_handler
    AS '?????' LANGUAGE C STRICT;

-- surely this thing is already written somewhere?
CREATE FUNCTION pgsql_fdw_validator(text[], oid)
    RETURNS void
    AS '????' LANGUAGE C STRICT;

CREATE FOREIGN DATA WRAPPER pgsql
    HANDLER pgsql_fdw_handler
    VALIDATOR pgsql_fdw_validator;

CREATE SERVER products_remote FOREIGN DATA WRAPPER pgsql OPTIONS
    (host 'localhost', dbname 'product_db', port '1234');

CREATE USER MAPPING FOR CURRENT_USER
    SERVER products_remote;

CREATE FOREIGN TABLE products (
  name text,
  id integer
) SERVER products_remote;

select * from products;

I can't figure out what to do for the handler and validator functions, do they exist somewhere? Do I need to write them myself? (what would that look like?) And would appreciate a validation of the overall approach.

I did find this contrib module, but it's apparently incomplete.

The dblink module is better suited for that: postgresql.org/docs/current/static/dblink.html — user330315
– user330315, Commented Jul 26, 2012 at 16:01
Thanks, that's what I needed. If you make it a real answer, I'll accept it. — Joshua Cheek
– Joshua Cheek, Commented Jul 26, 2012 at 17:49

Chris Travers · Accepted Answer · 2013-04-06 10:25:41Z

1

First, you need to get the pgsql_fdw code. I could not find a tarball so you are stuck with a git clone. To get it:

git clone git://interdbconnect.git.sourceforge.net/gitroot/interdbconnect/interdbconnect
cd interdbconnect/pgsql_fdw
make USE_PGXS=1
make USE_PGXS=1 install;

Then log into pgsql

CREATE EXTENSION pgsql_fdw;

create foreign data wrapper postgresql  handler pgsql_fdw_handler;
create server lsmb foreign data wrapper postgresql options (host 'localhost', dbname  'mtech_test', port '5432');
create user mapping for current_user server lsmb;
create foreign table defaults (setting_key text, value text) server lsmb;
select * from defaults;

Then this works for me.

I don't know whether pgsql_fdw was included in contrib in 9.2.

answered Apr 6, 2013 at 10:25

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jake Stevens-Haas Over a year ago

Is this answer still accurate? I think FDW has shipped with POSTGRES since 9.3, but it still asks for a handler function, and the documentation is opaque

Collectives™ on Stack Overflow

How to create Postgres to Postgres foreign data table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related