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