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

The following bug has been logged online:

Bug reference:      1309
Logged by:          Christina Zhang

Email address:      czhang@theinformationforge.com

PostgreSQL version: 7.4.1

Operating system:   Linux

Description:        PL/PGSQL function: ORDER BY does not accept variables

Details:

When we write a function using PL/PGSQL:

CREATE OR REPLACE FUNCTION uf_TestSearch(integer,varchar)RETURNS setof
ut_TestSearch
AS
'
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;
        RETURN;

RETURN;

END;
'LANGUAGE 'plpgsql';

Problem: When I use PERFORM uf_TestSearch(1,'ASC');
The returned result set are always sorted by "Column2", no matter what is
passed in the first parameter.

Could you please check whether the ORDER BY works correctly or not?

Thank you,

Christina

"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