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