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 unnamed connection 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