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

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.