Re: select across two database - Mailing list pgsql-sql

Helo

it is possible if you don't mind some work :)
We are doing it with plproxy.
Simple scenario would be
1. install plproxy
2. create sql functon with needed sql in remote db
3. create plproxy function in current db
4. create sql or function that combines the results from data in current db and plproxy function

regards,
Asko
skype: askoja

postgres@data2 ~$ createdb oltpdb 
CREATE DATABASE
postgres@data2 ~$ createdb archdb
CREATE DATABASE
postgres@data2 ~$ psql oltpdb < /usr/share/postgresql/8.2/contrib/plproxy.sql
CREATE FUNCTION
CREATE LANGUAGE

archdb=# create table archive ( data text );
CREATE TABLE
archdb=# insert into archive values ('archive row 1');
INSERT 0 1
archdb=# insert into archive values ('archive row 2');
INSERT 0 1
archdb=# insert into archive values ('archive row 3');
INSERT 0 1

archdb=# create function get_archive_data() returns setof text as $$ select data from archive; $$ language sql;
CREATE FUNCTION

oltpdb=# create table online ( data text );
CREATE TABLE
oltpdb=# insert into online values ('online row');
INSERT 0 1

oltpdb=# create function get_archive_data() returns setof text as $$ connect 'dbname=archdb'; $$ language plproxy;
CREATE FUNCTION

oltpdb=# create view all_data as select data from online union all select get_archive_data as data  from get_archive_data();
CREATE VIEW
oltpdb=# select * from all_data;
     data     
---------------
 online row
 archive row 1
 archive row 2
 archive row 3
(4 rows)