Message-ID: <468A47F2.2030000@archonet.com>
Date: Tue, 03 Jul 2007 13:58:26 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Loredana Curugiu <loredana.curugiu@gmail.com>
Cc: pgsql-sql@postgresql.org, pgsql-novice@postgresql.org
Subject: Re: dblink inside plpgsql function
References: <1c23c8e70707030202n323ee171n20f39cdece5b3618@mail.gmail.com>	
	<162867790707030213h7c81e498p1b5f9d7c9cf1cc9b@mail.gmail.com>	
	<468A36F0.A3DD.0030.0@indicator.be>	
	<1c23c8e70707030525m59ee79d0r425813cd93131bbc@mail.gmail.com>	
	<468A41CD.2070208@archonet.com>
	<1c23c8e70707030550j69f1911dj6cf33ab0a7db4776@mail.gmail.com>
In-Reply-To: <1c23c8e70707030550j69f1911dj6cf33ab0a7db4776@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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