CODEWITHSUNDEEP
postgresql
Joshua Cheek
Joshua Cheek

Reputation: 31726

How to create Postgres to Postgres foreign data table

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.

Upvotes: 2

Views: 4968

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

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.

Upvotes: 1

Related Questions