Re: Linking 2 or more databases.

Hi All,

I've got the contrib/dblink example working perfectly when connecting
to only one remote database, but when I wanna connect to two remote
databases, the last one screws up. I have been looking at the dblink code
and my SQL over and over, but to me it looks like it should work. Here are
the SQL:

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
select project, employee,
dblink_tok(t2.dbl_p,1) as name,
startdato, slutdato,
dblink_tok(t1.dblink_p,0) as f1,
substr( dblink_tok(t1.dblink_p,1), 1, 20) as f2
from
(select dblink('hostaddr=192.168.10.1 dbname=db1',
'select gruppe, beskrv from dokumentno where klasse = 0 and dokno = 0')
as dblink_p)
as t1,
(select dblink('hostaddr=192.168.10.1 dbname=db2',
'select employee, firstname from employee')
as dbl_p)
as t2,
timesched
where
dblink_tok(t1.dblink_p,0) = project and
dblink_tok(t2.dbl_p,0) = employee
order by
employee, project, startdato;
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

and this is the result (partly):

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
project | employee | name | startdato | slutdato | f1 | f2
---------+----------+------+------------+------------+-----+-------
167 | 44 | | 2002-10-14 | 2002-10-25 | 167 | Text 1
173 | 44 | | 2002-10-28 | 2002-11-01 | 173 | Text 2
181 | 44 | | 2002-10-14 | 2002-11-29 | 181 | Text 3
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

and in the log file:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
row number 102 is out of range 0..101
row number 102 is out of range 0..101
row number 102 is out of range 0..101
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

It clearly doesn't find the data of the second database/table (t2), but
I can't figure out why. If I switch the order of the tables in the from
clause, the problem is still in the second one (now t1). If only link to
one of them, it works nicely on either of them.

Please help,

Leif

On Wed, 23 Oct 2002, Leif Jensen wrote:

>
> Hi Bruce,
>
> Thank you for you quick response. Just what I needed :-).
>
> I just thought this was part of the SQL standard !?
>
> Leif
>
>
>
> On Tue, 22 Oct 2002, Bruce Momjian wrote:
>
> >
> > See FAQ item 4.24. See the web site version because it is updated.
> >
> > ---------------------------------------------------------------------------
> >
> > Leif Jensen wrote:
> > >
> > > Hi All,
> > >
> > > I need to get information from 2 (or more) database preferably in one
> > > select statement. Is there a way to join tables from different databases
> > > in the same SQL ? Can I have a 'virtual' foreign key ? Can I make a view
> > > 'cross database' ? Can I make some server side programming to accomplish
> > > this task ? Where do I find documentation if any ?
> > >
> > > Thanks for any ideas,
> > >
> > > Leif
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> >
> > --
> > Bruce Momjian | http://candle.pha.pa.us
> > pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> > + If your life is a hard drive, | 13 Roberts Road
> > + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>