Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 2EBEA9FB212
	for <pgsql-novice-postgresql.org@postgresql.org>;
	Tue,  3 Jul 2007 09:50:29 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 00962-06 for <pgsql-novice-postgresql.org@postgresql.org>;
	Tue,  3 Jul 2007 09:50:15 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from an-out-0708.google.com (an-out-0708.google.com
 [209.85.132.242])
	by postgresql.org (Postfix) with ESMTP id 5A5A29FB1FE
	for <pgsql-novice@postgresql.org>; Tue,  3 Jul 2007 09:50:20 -0300 (ADT)
Received: by an-out-0708.google.com with SMTP id c36so369102ana
	for <pgsql-novice@postgresql.org>; Tue, 03 Jul 2007 05:50:19 -0700 (PDT)
DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta;
	h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
	b=cEAOlyL/YAZ5ozvq6K1CN42us5K0j0XGm1Sgx2L83EnoEcGlEkEInJ3MwI1kSDbj6ShTGs5vL3obt4yLihZKPxKzuKp3L6qgMDgz7oyol3+SVlpkVHcEAlnuEpKSNUSB5JdBgavWa2oBF0XpdQ5WaRoLQXsxHI6zbAxx7HFeTJk=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
	b=abtGAzNxk8VE7lTdh5Bzn7n9tqJLsMDSPC4HHPeo0L/U6021+5sQNZt4P/3c/dszrQ5hP2DFod+8iAfxPchyEWVQJ/0sJkl5M6lgw1b/a1v3luxvuRWiR0YTrbKGp7HrdMH7UCDsKHxlYhlxSJeEdzZQSrg8NdwSyWRUPi2LV4U=
Received: by 10.100.107.2 with SMTP id f2mr4271152anc.1183467019002;
	Tue, 03 Jul 2007 05:50:19 -0700 (PDT)
Received: by 10.100.194.7 with HTTP; Tue, 3 Jul 2007 05:50:18 -0700 (PDT)
Message-ID: <1c23c8e70707030550j69f1911dj6cf33ab0a7db4776@mail.gmail.com>
Date: Tue, 3 Jul 2007 15:50:18 +0300
From: "Loredana Curugiu" <loredana.curugiu@gmail.com>
To: "Richard Huxton" <dev@archonet.com>, pgsql-sql@postgresql.org,
	pgsql-novice@postgresql.org
Subject: Re: [SQL] dblink inside plpgsql function
In-Reply-To: <468A41CD.2070208@archonet.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_123048_5737294.1183467018967"
References: <1c23c8e70707030202n323ee171n20f39cdece5b3618@mail.gmail.com>
	<162867790707030213h7c81e498p1b5f9d7c9cf1cc9b@mail.gmail.com>
	<468A36F0.A3DD.0030.0@indicator.be>
	<1c23c8e70707030525m59ee79d0r425813cd93131bbc@mail.gmail.com>
	<468A41CD.2070208@archonet.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/14
X-Sequence-Number: 19100

------=_Part_123048_5737294.1183467018967
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

CREATE OR REPLACE FUNCTION getReminderServices( varchar ) RETURNS SETOF
reminder_services AS'

DECLARE r reminder_services%ROWTYPE;

BEGIN
    PERFORM dblink_connect(''dbname=''||$1);
    FOR r IN SELECT * FROM dblink(''SELECT * FROM reminder_services'')
               AS columns( uid INT,
                                     theme_uid INT,
                                     activity_MT_amount INT,
                                     activity_min_days INT,
                                     activity_max_months INT,
                                     inactivity_days INT,
                                     limit_reminders INT,
                                     limit_months INT,
                                     scanning_time TIMESTAMP WITH TIME ZONE,
                                     reminder_time TIMESTAMP WITH TIME ZONE,
                                     message TEXT)
    LOOP
    RETURN NEXT r;
    END LOOP;
    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

------=_Part_123048_5737294.1183467018967
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

CREATE OR REPLACE FUNCTION getReminderServices( varchar ) RETURNS SETOF reminder_services AS&#39;<br>

<br>

DECLARE r reminder_services%ROWTYPE;<br>

<br>

BEGIN<br>

&nbsp;&nbsp;&nbsp; PERFORM dblink_connect(&#39;&#39;dbname=&#39;&#39;||$1);<br>

&nbsp;&nbsp;&nbsp; FOR r IN SELECT * FROM dblink(&#39;&#39;SELECT * FROM reminder_services&#39;&#39;)<br>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AS columns( uid INT,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
theme_uid INT,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
activity_MT_amount INT,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
activity_min_days INT,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
activity_max_months INT,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
inactivity_days INT,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
limit_reminders INT,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
limit_months INT,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
scanning_time TIMESTAMP WITH TIME ZONE,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
reminder_time TIMESTAMP WITH TIME ZONE,<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
message TEXT)<br>

&nbsp;&nbsp;&nbsp; LOOP<br>

&nbsp;&nbsp;&nbsp; RETURN NEXT r;<br>

&nbsp;&nbsp;&nbsp; END LOOP;<br>

&nbsp;&nbsp;&nbsp; PERFORM dblink_disconnect($1);<br>

&nbsp;&nbsp;&nbsp; RETURN;<br>

END;<br>

&#39; LANGUAGE plpgsql; <br>

<br>
Now I get the errors: <br>
<br>
<span style="color: rgb(204, 0, 0);">connection &quot;eu&quot; not available</span><br style="color: rgb(204, 0, 0);">
<span style="color: rgb(204, 0, 0);">CONTEXT:&nbsp; SQL statement &quot;SELECT&nbsp; dblink_disconnect( $1 )&quot;</span><br style="color: rgb(204, 0, 0);">
<span style="color: rgb(204, 0, 0);">PL/pgSQL function &quot;getreminderservices&quot; line 21 at perform</span><br style="color: rgb(204, 0, 0);">
<br style="color: rgb(204, 0, 0);"><div><div><br>
<br>
&nbsp;</div><br></div><br>

------=_Part_123048_5737294.1183467018967--