Re: function retuning refcursor, order by ignored?

Yes, thanks, you're all very helpful and I completely appreciate it.

For future reference, here is the adapted procedure. I wonder if the way I'm
dealing with the boolean param (using the if then to set a stand in
variable) is as clean as it could be...but it does work.

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;
strSQL varchar;
strActive varchar;
BEGIN
if "pbool_active" = true then
strActive = 'true';
else
strActive = 'false';
end if;

strSQL := 'SELECT item_id, item_name, item_org_id, item_active
FROM public.t_item
WHERE item_org_id = ' || "pint_org_id" || ' and item_active = ' ||
strActive ||
' ORDER BY ' || "pstr_orderby";
OPEN ref FOR EXECUTE strSQL;
RETURN ref;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Alvaro Herrera-7 wrote:
>
> novnov escribió:
>>
>> 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).
>
> It doesn't because the value is expanded as a constant, therefore all
> rows have the same value and the sort is a no-op. Try using EXECUTE
> (although I admit I don't know if you are able to do an OPEN CURSOR with
> EXECUTE)
>
>
> --
> Alvaro Herrera
> http://www.amazon.com/gp/registry/CTMLCN8V17R4
> Bob [Floyd] used to say that he was planning to get a Ph.D. by the "green
> stamp method," namely by saving envelopes addressed to him as 'Dr. Floyd'.
> After collecting 500 such letters, he mused, a university somewhere in
> Arizona would probably grant him a degree. (Don Knuth)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/
>
>

--
View this message in context: http://www.nabble.com/function-retuning-refcursor%2C-order-by-ignored--tf3837519.html#a10881030
Sent from the PostgreSQL - general mailing list archive at Nabble.com.