Re: How To Connect to another Database inside a plpgsql function

[Mark Gibson <gibsonm@xxxxxxxxxxxxxx>]

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.