Re: BUG #1309: PL/PGSQL function: ORDER BY does not accept variables - Mailing list pgsql-bugs

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.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