Search Postgresql Archives

Re: "RETURN QUERY" mystery

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux