Thread

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

   PostgreSQL Bugs List <pgsql-bugs@postgresql.org> — 2004-11-09T22:15:10Z

   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
   
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-09T22:48:15Z

   "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