While a hard coded order by clause works; passing the order by as a param is
ignored as I've implemented below. The order by value is being passed as
expected (tested by outputing the value in a column). I've called like so:
ups=# begin;
BEGIN
ups=# select proc_item_list(1,true,'item_id');
proc_item_list
---------------------
<unnamed portal 12>
(1 row)
ups=# fetch all from "<unnamed portal 12>";
CREATE or REPLACE FUNCTION "public"."proc_item_list"(
IN "pint_org_id" int4,
IN "pbool_active" bool,
IN "pstr_orderby" varchar)
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 "pstr_orderby";
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#a10879984
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
