Thread: How To Connect to another Database inside a plpgsql function
Hi Guys!
I have 2 database namely "DB1" and "DB2".
In DB1 I created a plgpsql function fnTest().
My problem is inside fnTest() I need to check some tables in DB2! I've no idea how to do this. Usually when I make a function I only access tables/views/functions in the same database. I really don't know if this is possible.
BTW, I googled my problem but did not see any topics on this.
Need your expertise on this one guys...TIA!
Marie Gezeala M. Bacuño II
IS Department
Muramoto Audio-Visual Phils., Inc.
MEPZ1, Lapu-Lapu City, Cebu, Philippines 6015
The person with the ultimate cachinnation possesses, thereby, the optimal cachinnation.
Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now
Gezeala 'Eyah' Bacuño II wrote:
> Hi Guys!
>
> I have 2 database namely "DB1" and "DB2".
>
> In DB1 I created a plgpsql function fnTest().
>
> My problem is inside fnTest() I need to check some tables in DB2! I've
> no idea how to do this. Usually when I make a function I only access
> tables/views/functions in the same database. I really don't know if
> this is possible.
>
Take a look at contrib/dblink, it allows cross database queries like this:
SELECT *
FROM
dblink('host=myhost dbname=DB1 user=me',
'SELECT col1, col2, col3
FROM mytable')
AS t (col1 integer, col2 text, col3 real);
--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.
Re: How To Connect to another Database inside a plpgsql function
From
Gezeala 'Eyah' Bacuño II
Date:
Sir, I would really appreciate it if you can please expound more on how to issue a "connect" inside a plpgsql function??
Daghang Salamat sa iyong prompt reply..
I apologize for the late reply kase wala kaming work last saturday.
BTW, I'm not that good in bisaya that's why combination ng bisaya, tagalog and english ang reply ko ;)
Manuel Cabido <manny@tinago.msuiit.edu.ph> wrote:
Hi Eyah,
Try daw ug issue ug "connect" to db2 within your procedure in db1.
Manny
Marie Gezeala M. Bacuño II
IS Department
Muramoto Audio-Visual Phils., Inc.
MEPZ1, Lapu-Lapu City, Cebu, Philippines 6015
The person with the ultimate cachinnation possesses, thereby, the optimal cachinnation.
Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now