Re: dblink inside plpgsql function

From: "Loredana Curugiu" <loredana(dot)curugiu(at)gmail(dot)com>
To: "Bart Degryse" <Bart(dot)Degryse(at)indicator(dot)be>, "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: dblink inside plpgsql function
Date: 2007-07-03 12:25:51
Message-ID: 1c23c8e70707030525m59ee79d0r425813cd93131bbc@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice pgsql-sql

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;

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Richard Huxton 2007-07-03 12:32:13 Re: dblink inside plpgsql function
Previous Message Tiemo Kieft 2007-07-03 10:17:16 Multiple inserts into 2 tables with FKs

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2007-07-03 12:32:13 Re: dblink inside plpgsql function
Previous Message Bart Degryse 2007-07-03 09:45:53 Re: dblink inside plpgsql function