Re: select across two database

------=_Part_37145_1996887.1213662228239
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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@[EMAIL PROTECTED]
 ~$ createdb oltpdb
CREATE DATABASE
postgres@[EMAIL PROTECTED]
 ~$ createdb archdb
CREATE DATABASE
postgres@[EMAIL PROTECTED]
 ~$ 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.groups@[EMAIL PROTECTED]
>
wrote:

> On 17/06/2008, Jorge Medina <jorge@[EMAIL PROTECTED]
> 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@[EMAIL PROTECTED]
)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

------=_Part_37145_1996887.1213662228239
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Helo <br><br>it is possible if you don&#39;t mind some work :)<br>We are
doing it with plproxy. <br>Simple scenario would be<br>1. install
plproxy<br>2. create sql functon with needed sql in remote db<br>3. create
plproxy function in current db<br>
4. create sql or function that combines the results from data in current
db and plproxy function<br><br>regards,<br>Asko<br>skype:
askoja<br><br>postgres@[EMAIL PROTECTED]
 ~$ createdb oltpdb&nbsp; <br>CREATE
DATABASE<br>postgres@[EMAIL PROTECTED]
 ~$ createdb archdb<br>
CREATE DATABASE<br>postgres@[EMAIL PROTECTED]
 ~$ psql oltpdb &lt;
/usr/share/postgresql/8.2/contrib/plproxy.sql<br>CREATE FUNCTION<br>CREATE
LANGUAGE<br><br>archdb=# create table archive ( data text );<br>
CREATE TABLE<br>
archdb=# insert into archive values (&#39;archive row 1&#39;);<br>
INSERT 0 1<br>
archdb=# insert into archive values (&#39;archive row 2&#39;);<br>
INSERT 0 1<br>
archdb=# insert into archive values (&#39;archive row 3&#39;);<br>
INSERT 0 1<br>
<br>archdb=# create function get_archive_data() returns setof text as $$
select data from archive; $$ language sql;<br>
CREATE FUNCTION<br>
<br>oltpdb=# create table online ( data text );<br>CREATE
TABLE<br>oltpdb=# insert into online values (&#39;online
row&#39;);<br>INSERT 0 1<br><br>oltpdb=# create function
get_archive_data() returns setof text as $$ connect
&#39;dbname=archdb&#39;; $$ language plproxy;<br>
CREATE FUNCTION<br><br>oltpdb=# create view all_data as select data from
online union all select get_archive_data as data&nbsp; from
get_archive_data();<br>CREATE VIEW<br>oltpdb=# select * from
all_data;<br>&nbsp;&nbsp;&nbsp;&nbsp; data&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<br>
---------------<br>&nbsp;online row<br>&nbsp;archive row
1<br>&nbsp;archive row 2<br>&nbsp;archive row 3<br>(4
rows)<br><br><br><div class="gmail_quote">On Tue, Jun 17, 2008 at 12:55
AM, Andrej Ricnik-Bay &lt;<a
href="mailto:andrej.groups@[EMAIL PROTECTED]
">andrej.groups@[EMAIL PROTECTED]
>&gt;
wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204,
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div
class="Ih2E3d">On 17/06/2008, Jorge Medina &lt;<a
href="mailto:jorge@[EMAIL PROTECTED]
">jorge@[EMAIL PROTECTED]
>&gt; wrote:<br>

&gt; hi guys.<br>
&gt; &nbsp;I want know if it&#39;s possible create a select from 2
database or create<br>
&gt; &nbsp;a view in one of them.<br>
</div>The short answer is no.<br>
<div><div></div><div class="Wj3C7c"><br>
--<br>
Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@[EMAIL PROTECTED]
">pgsql-sql@[EMAIL PROTECTED]
>)<br>
To make changes to your subscription:<br>
<a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br>
</div></div></blockquote></div><br>

------=_Part_37145_1996887.1213662228239--