It seems that at least in the way I've written the function below, ORDER BY
is ignored. I've seen hints that one can declare the refcursor as a specific
query and that apparently the order by clause there is respected. But I
don't find much by way of examples in the docs or on this list. I will
eventually need LIMIT and OFFSET as well as ORDER BY. It would be extremely
helpful if someone could take the function below and rearrange so that it
supports ORDER BY, LIMIT and OFFSET.
CREATE or REPLACE FUNCTION "public"."proc_item_list"(
IN "pint_org_id" int4,
IN "pbool_active" bool)
RETURNS "pg_catalog"."refcursor" AS
$BODY$
DECLARE
ref refcursor;
BEGIN
OPEN ref FOR SELECT item_id, item_name, item_org_id, item_active
FROM public.t_item
WHERE item_org_id = "pint_org_id" and item_active = "pbool_active"
ORDER BY item_id;
RETURN ref;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
View this message in context:
http://www.nabble.com/function-retuning-refcursor%2C-order-by-ignored--tf3837519.html#a10865322
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
