Re: dblink inside plpgsql function

Loredana Curugiu wrote:
> CREATE OR REPLACE FUNCTION getReminderServices( varchar ) RETURNS SETOF
> reminder_services AS'
> 
> DECLARE r reminder_services%ROWTYPE;
> 
> BEGIN
>    PERFORM dblink_connect(''dbname=''||$1);

>    PERFORM dblink_disconnect($1);
>    RETURN;
> END;
> ' LANGUAGE plpgsql;
> 
> Now I get the errors:
> 
> connection "eu" not available
> CONTEXT:  SQL statement "SELECT  dblink_disconnect( $1 )"
> PL/pgSQL function "getreminderservices" line 21 at perform

Well, it's complaining that a connection called "eu" isn't available in 
the dblink_disconnect() call. I don't use dblink much myself, so I 
approached this problem by looking in the documentation.

According to the docs, there are two ways to call dblink_disconnect()
"
Synopsis

dblink_disconnect()
dblink_disconnect(text connname)

Inputs
  connname    if an argument is given, it is used as a name for a persistent    connection to close; otherwiase the
unnamedconnection is closed
 
"

So - did we open a connection called "eu"? Looking at the docs again, it 
appears no! If we use the one-argument version of dblink_connect() we 
get an unnamed connection.

So - either change the dblink_disconnect so there is no argument:  dblink_disconnect()

Or change the connection  dblink_connect($1, ''dbname='' || $1)

I'd do the first one, since you don't care what the connection is called 
and are closing it at the end of the function.

HTH

--   Richard Huxton  Archonet Ltd