Re: Order by parameter inside pgsql function ignored

On Tuesday 2. June 2009, Anton Marchenkov wrote:
>CREATE OR REPLACE FUNCTION "public"."test_order_by" (sort_key varchar)
>RETURNS SETOF "customers"."customers_with_mark_deleted" AS
>$body$
>DECLARE
> rec RECORD;
>BEGIN
> FOR rec IN SELECT * FROM customers.customers_with_mark_deleted c
> ORDER BY sort_key ASC
> LOOP
> RETURN NEXT rec;
> END LOOP;
> RETURN;
>END;
>$body$
>LANGUAGE 'plpgsql'
>VOLATILE
>CALLED ON NULL INPUT
>SECURITY INVOKER
>COST 100 ROWS 1000;

By the way, there's no need to declare this function as VOLATILE, as it
doesn't change anything in the database. STABLE will do just fine.

I wrote:
FOR rec IN EXECUTE
'SELECT * FROM customers.customers_with_mark_deleted c  ORDER BY ' ||
sort_key || ' ASC'

In case you're expecting CamelCased column names, you should also use
the quote_ident() function:

FOR rec IN EXECUTE
'SELECT * FROM customers_with_mark_deleted ORDER BY ' ||
quote_ident(sort_key) || ' ASC' LOOP
...
--
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/