Re: BUG #1309: PL/PGSQL function: ORDER BY does not accept variables

"PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> writes:
> DECLARE
> v_MyRow ut_TestSearch%rowtype;
> a_OrderBy ALIAS FOR $1;
> a_SortAsc ALIAS FOR $2;
> BEGIN
> FOR v_MyRow IN
> SELECT Colum1,
> Column2,
> Column3
> FROM Table1
> ORDER BY a_OrderBy a_SortAsc
> LOOP
> RETURN NEXT v_MyRow;
> END LOOP;

You could make that work using FOR ... IN EXECUTE, but as-is it's a syntax
error. You can't use plpgsql variables to interpolate keywords, or even
column names into a regular SQL command; they are *values* and nothing
more. (Indeed it would be exceedingly dangerous if they worked the way
you're supposing.)

Something like

FOR v_MyRow IN EXECUTE
''SELECT Colum1,
Column2,
Column3
FROM Table1
ORDER BY '' || quote_identifier(a_OrderBy) || '' '' || a_SortAsc
LOOP

would do what you intended.

regards, tom lane