[SQL] Ordering a record returned from a stored procedure

[Kent Anderson]

I am pulling a report from the database using a stored procedure but cannot get the information to return in a specific order unless I hardcode the order by clause.

 

CREATE OR REPLACE FUNCTION submissionreport(INTEGER, DATE, TEXT) RETURNS setof submissionrec AS '
DECLARE
                result submissionrec%rowtype;
                hmhmkey ALIAS for $1;
                submissiondate ALIAS for $2;
                sort ALIAS for $3;

 

BEGIN

RAISE NOTICE ''The sort order should be: %.'', sort;

FOR result IN
 SELECT

    (..... select all necessary fields ...)

FROM      

    (.... tables ...)

WHERE   

    (... contraints)

 

ORDER BY sort
LOOP
        RETURN next result;
END LOOP;

 

 RETURN result;

 

END;
' LANGUAGE plpgsql;

 

What am I missing? The returned data is ordered if the "Order By" clause has the values hard coded but doesn't seem to read the "sort" variable.

 

Any help would be appreciated.

                        

 

Kent Anderson