On Feb 6, 7:45 pm, dima <dejan.di...@xxxxxxxxx> 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;
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
