From: | Dejan Dimic <dejan(dot)dimic(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: "RETURN QUERY" mystery |
Date: | 2008-02-07 19:56:13 |
Message-ID: | b746fa55-e2d9-45a7-889c-9d46c44f34ba@j78g2000hsd.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Feb 6, 7:45 pm, dima <dejan(dot)di(dot)(dot)(dot)(at)gmail(dot)com> wrote:
> Can anybody give us more detailed explanation about "RETURN QUERY"
> syntax and usage with possible some non trivial samples.
>
> Thanks in advance.
I somehow get it.
Here is one of my examples that, perhaps, could help someone that
doesn't se it at first glimpse.
CREATE OR REPLACE FUNCTION get_index_dif(indexid integer, use_daily
boolean, up_time_limit timestamp with time zone, use_up_time_limit
boolean, start_time timestamp with time zone, use_start_time boolean)
RETURNS SETOF get_range_index_values_result AS
$BODY$
DECLARE
indexid ALIAS FOR $1;
use_daily ALIAS FOR $2;
up_time_limit ALIAS FOR $3;
use_up_time_limit ALIAS FOR $4;
start_time ALIAS FOR $5;
use_start_time ALIAS FOR $6;
end_time timestamp;
BEGIN
IF use_daily THEN
select max(event_time) into end_time FROM index_daily WHERE
index_id = indexid;
ELSE
select max(event_time) into end_time FROM index_minute WHERE
index_id = indexid;
END IF;
IF end_time IS NOT NULL THEN
IF use_daily THEN
RETURN QUERY (SELECT index_value, event_time FROM index_values
WHERE index_id = indexid AND date_trunc('day', event_time) > end_time
AND
CASE WHEN use_start_time THEN event_time >= start_time ELSE true
END AND
CASE WHEN use_up_time_limit THEN event_time <= up_time_limit ELSE
true END
ORDER BY event_time DESC);
ELSE
end_time := end_time + interval '1 minute';
IF use_start_time AND start_time > end_time THEN end_time :=
start_time; END IF;
RETURN QUERY (SELECT index_value, event_time FROM index_values
WHERE index_id = indexid AND event_time >= end_time AND
CASE WHEN use_up_time_limit THEN event_time <= up_time_limit ELSE
true END
ORDER BY event_time DESC);
END IF;
ELSE
RETURN QUERY (SELECT index_value, event_time FROM index_values
WHERE index_id = indexid AND
CASE WHEN use_start_time THEN event_time >= start_time ELSE true
END AND
CASE WHEN use_up_time_limit THEN event_time <= up_time_limit ELSE
true END
ORDER BY event_time DESC);
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION get_index_dif(integer, boolean, timestamp with time
zone, boolean, timestamp with time zone, boolean) OWNER TO root;
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Colson | 2008-02-07 22:50:36 | Re: offline* |
Previous Message | LARC/J.L.Shipman/jshipman | 2008-02-07 19:07:11 | offline* |