Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 0A5749FB545
	for <pgsql-novice-postgresql.org@postgresql.org>;
	Tue,  3 Jul 2007 06:03:03 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 64190-01 for <pgsql-novice-postgresql.org@postgresql.org>;
	Tue,  3 Jul 2007 06:02:53 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from an-out-0708.google.com (an-out-0708.google.com
 [209.85.132.248])
	by postgresql.org (Postfix) with ESMTP id B3B139FB4B6
	for <pgsql-novice@postgresql.org>; Tue,  3 Jul 2007 06:02:56 -0300 (ADT)
Received: by an-out-0708.google.com with SMTP id c36so358838ana
	for <pgsql-novice@postgresql.org>; Tue, 03 Jul 2007 02:02:55 -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:mime-version:content-type;
	b=OaIT2CdvkskTCcMT0b7Dm1WEBR+gXnDPEeqWt8uQWbd+d2v0Ku81EW0NRirWKw4QMy8pbXcFDNdwqHq8iYsqKIM8FsxlOMduC7Y23jOfGUFRSh2OV/OCnjvqd2PdNMKCpojh0Cq8CeINJ/0je7fNXEH1ohiYAeK8PTZJCHTQZ1I=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=QddKBxw1jI6XhTNdznyrJJvbdRKeH+tViIxM6dKOAhGUG/l9tiF3bM2JxhJNQUG7RHjvD/Mjs5xsA4BbLSdKQS+mV4BRyNvngZlCqCub1OPgLbQsEbw25QzRDFotXHhXc3ixhUshKy9IyhUAPQoBvCOhLo89KctzcLAU4hDh5VU=
Received: by 10.100.112.19 with SMTP id k19mr4188098anc.1183453375171;
	Tue, 03 Jul 2007 02:02:55 -0700 (PDT)
Received: by 10.100.194.7 with HTTP; Tue, 3 Jul 2007 02:02:54 -0700 (PDT)
Message-ID: <1c23c8e70707030202n323ee171n20f39cdece5b3618@mail.gmail.com>
Date: Tue, 3 Jul 2007 12:02:54 +0300
From: "Loredana Curugiu" <loredana.curugiu@gmail.com>
To: pgsql-novice@postgresql.org, pgsql-sql@postgresql.org
Subject: dblink inside plpgsql function
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_120003_18569079.1183453374983"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/12
X-Sequence-Number: 19098

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

Hi everybody,

I created the following function:

CREATE OR REPLACE FUNCTION GetReminderServices( varchar ) RETURNS SETOF
reminder_services AS'
BEGIN
    SELECT dblink_connect(''dbname=''||$1);
    SELECT * FROM dblink(''SELECT * FROM reminder_services'')
               AS reminder_services( 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);
    SELECT dblink_disconnect($1);
    RETURN;
END;
' LANGUAGE plpgsql;

When I call this function as SELECT * FROM GetReminderServices('eu');
I get the following errors:
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "getreminderservices" line 2 at SQL statement

Does somebody know which is the problem?


Best,
     Loredana

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

Hi everybody,<br>
<br>
I created the following function:<br>
<br>
CREATE OR REPLACE FUNCTION GetReminderServices( varchar ) RETURNS SETOF reminder_services AS&#39;<br>
BEGIN<br>
&nbsp;&nbsp;&nbsp; SELECT dblink_connect(&#39;&#39;dbname=&#39;&#39;||$1);<br>
&nbsp;&nbsp;&nbsp; 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 reminder_services( 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; SELECT dblink_disconnect($1);<br>
&nbsp;&nbsp;&nbsp; RETURN;&nbsp; <br>
END;<br>
&#39; LANGUAGE plpgsql; <br>
<br>
When I call this function as SELECT * FROM GetReminderServices(&#39;eu&#39;);<br>
I get the following errors:<br>
ERROR:&nbsp; query has no destination for result data<br>
HINT:&nbsp; If you want to discard the results of a SELECT, use PERFORM instead.<br>
CONTEXT:&nbsp; PL/pgSQL function &quot;getreminderservices&quot; line 2 at SQL statement<br>
<br>
Does somebody know which is the problem? <br>
<br>
<br>
Best, <br>
&nbsp;&nbsp;&nbsp;&nbsp; Loredana<br>
<br>
<br>
<br>

------=_Part_120003_18569079.1183453374983--