Re: [SQL] dblink inside plpgsql function

[Loredana Curugiu]

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;