From: Loredana Curugiu <loredana.curugiu@gmail.com>
To: Bart Degryse <Bart.Degryse@indicator.be>
To: Pavel Stehule <pavel.stehule@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: dblink inside plpgsql function
Date: Tue, 3 Jul 2007 15:25:51 +0300
Message-ID: <1c23c8e70707030525m59ee79d0r425813cd93131bbc@mail.gmail.com> (raw)
In-Reply-To: <468A36F0.A3DD.0030.0@indicator.be>
References: <1c23c8e70707030202n323ee171n20f39cdece5b3618@mail.gmail.com>
<162867790707030213h7c81e498p1b5f9d7c9cf1cc9b@mail.gmail.com>
<468A36F0.A3DD.0030.0@indicator.be>
I created the following function
CREATE OR REPLACE FUNCTION getReminderServices( varchar ) RETURNS SETOF
reminder_services AS'
DECLARE r reminder_services%ROWTYPE;
BEGIN
SELECT 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;
SELECT dblink_disconnect($1);
RETURN;
END;
' LANGUAGE plpgsql;
and I get the same errors. I think it is a problem with the dblink because
the following function it works fine if I call SELECT * FROM getReminders().
CREATE OR REPLACE FUNCTION getReminders() RETURNS SETOF reminder_services
AS'
DECLARE r reminder_services%ROWTYPE;
BEGIN
FOR r IN SELECT * FROM reminder_services
LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: loredana.curugiu@gmail.com, Bart.Degryse@indicator.be, pavel.stehule@gmail.com
Subject: Re: dblink inside plpgsql function
In-Reply-To: <1c23c8e70707030525m59ee79d0r425813cd93131bbc@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox