Thread: dblink inside plpgsql function
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
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
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
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
I should read the documentation carrefully .. Yes, you're right, Richard.
Many thanks to all.
Best,
Loredana
Many thanks to all.
Best,
Loredana