Postgres Pro
Downloads
Home   >   mailing lists

Thread: FOREIGN TABLE with dblink

FOREIGN TABLE with dblink

From
Jasmin Dizdarevic
Date:
Hi, 

is there any way to use the new foreign table feature with dblink?
That's almost clear to me:

CREATE FOREIGN DATA WRAPPER pgsql90;

CREATE SERVER srvlocal90 FOREIGN DATA WRAPPER pgsql90 OPTIONS (hostaddr '127.0.0.1', dbname 'lotty');

CREATE USER MAPPING FOR pgsql SERVER srvlocal90 OPTIONS (user 'pgsql', password '');

I would like to replace this

SELECT dblink_connect('myconn', 'srvlocal90');
SELECT * FROM dblink('myconn', 'select * from mytests.fttest') AS t(id int, myname text);

to something like this:

CREATE FOREIGN TABLE mytests.lnkto90_fttest (
id int,
myname text
)
SERVER srvlocal90 OPTIONS (????)

SELECT * FROM mytests.lnkto90_fttest;

Ty

Re: FOREIGN TABLE with dblink

From
Shigeru Hanada
Date:
Hi Jasmin,

(2011/06/16 19:40), Jasmin Dizdarevic wrote:
> Hi,
>
> is there any way to use the new foreign table feature with dblink?
> That's almost clear to me:
>
> CREATE FOREIGN DATA WRAPPER pgsql90;
>
> CREATE SERVER srvlocal90 FOREIGN DATA WRAPPER pgsql90 OPTIONS (hostaddr
> '127.0.0.1', dbname 'lotty');
>
> CREATE USER MAPPING FOR pgsql SERVER srvlocal90 OPTIONS (user 'pgsql',
> password '');
>
> I would like to replace this
>
> SELECT dblink_connect('myconn', 'srvlocal90');
> SELECT * FROM dblink('myconn', 'select * from mytests.fttest') AS t(id int,
> myname text);
>
> to something like this:
>
> CREATE FOREIGN TABLE mytests.lnkto90_fttest (
> id int,
> myname text
> )
> SERVER srvlocal90 OPTIONS (????)
>
> SELECT * FROM mytests.lnkto90_fttest;

Your assumption is exactly what the foreign table feature aims at;
foreign table feature would allow you to access external data via a SQL
statement which conformed with the SQL statement, though foreign tables
are read-only in the first release.

But unfortunately FDW for PostgreSQL won't be shipped with 9.1 release.
 Hopefully, PostgreSQL-FDW for 9.1 might be released as an external
module, and then you would be able to replace dblink with foreign tables.

Regards,
--
Shigeru Hanada

Re: FOREIGN TABLE with dblink

From
Jasmin Dizdarevic
Date:
Hi Shigeru, 

thank you for responding. This is going to be a great feature!

Regards,
Jasmin

2011/6/29 Shigeru Hanada <shigeru.hanada@gmail.com>
Hi Jasmin,

(2011/06/16 19:40), Jasmin Dizdarevic wrote:
> Hi,
>
> is there any way to use the new foreign table feature with dblink?
> That's almost clear to me:
>
> CREATE FOREIGN DATA WRAPPER pgsql90;
>
> CREATE SERVER srvlocal90 FOREIGN DATA WRAPPER pgsql90 OPTIONS (hostaddr
> '127.0.0.1', dbname 'lotty');
>
> CREATE USER MAPPING FOR pgsql SERVER srvlocal90 OPTIONS (user 'pgsql',
> password '');
>
> I would like to replace this
>
> SELECT dblink_connect('myconn', 'srvlocal90');
> SELECT * FROM dblink('myconn', 'select * from mytests.fttest') AS t(id int,
> myname text);
>
> to something like this:
>
> CREATE FOREIGN TABLE mytests.lnkto90_fttest (
> id int,
> myname text
> )
> SERVER srvlocal90 OPTIONS (????)
>
> SELECT * FROM mytests.lnkto90_fttest;

Your assumption is exactly what the foreign table feature aims at;
foreign table feature would allow you to access external data via a SQL
statement which conformed with the SQL statement, though foreign tables
are read-only in the first release.

But unfortunately FDW for PostgreSQL won't be shipped with 9.1 release.
 Hopefully, PostgreSQL-FDW for 9.1 might be released as an external
module, and then you would be able to replace dblink with foreign tables.

Regards,
--
Shigeru Hanada