Re: select across two database

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(at)data2 ~$ createdb oltpdb
CREATE DATABASE
postgres(at)data2 ~$ createdb archdb
CREATE DATABASE
postgres(at)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)

On Tue, Jun 17, 2008 at 12:55 AM, Andrej Ricnik-Bay <andrej(dot)groups(at)gmail(dot)com>
wrote:

> On 17/06/2008, Jorge Medina <jorge(at)bsdchile(dot)cl> wrote:
> > hi guys.
> > I want know if it's possible create a select from 2 database or create
> > a view in one of them.
> The short answer is no.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>