From: Pavel Stehule <pavel.stehule@gmail.com>
To: Loredana Curugiu <loredana.curugiu@gmail.com>
Cc: pgsql-novice@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: dblink inside plpgsql function
Date: Tue, 3 Jul 2007 11:13:42 +0200
Message-ID: <162867790707030213h7c81e498p1b5f9d7c9cf1cc9b@mail.gmail.com> (raw)
In-Reply-To: <1c23c8e70707030202n323ee171n20f39cdece5b3618@mail.gmail.com>
References: <1c23c8e70707030202n323ee171n20f39cdece5b3618@mail.gmail.com>
Hello
Every SELECT statement in PL/pgSQL have to be forward to variables. In
plpgsql you can you use select only like
select into variables columns from ...
propably better version is (i haven't installed dblink and can't to test it)
CREATE OR REPLACE FUNCTION GetReminderServices( varchar ) RETURNS
SETOF reminder_services AS'
DECLARE r record;
BEGIN
PERFORM dblink_connect(''dbname=''||$1);
FOR r IN 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)
LOOP
RETURN NEXT r;
END LOOP;
PERFORM dblink_disconnect($1);
RETURN
END;
' LANGUAGE plpgsql;
regards
Pavel
2007/7/3, Loredana Curugiu <loredana.curugiu@gmail.com>:
> 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
>
>
>
>
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: pavel.stehule@gmail.com, loredana.curugiu@gmail.com, pgsql-novice@postgresql.org
Subject: Re: dblink inside plpgsql function
In-Reply-To: <162867790707030213h7c81e498p1b5f9d7c9cf1cc9b@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