Re: Ordering a record returned from a stored procedure

On Mon, 18 Oct 2004, Kent Anderson wrote:

> 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.

You're telling it to order by the value of the third argument, not the
value of the column with the name of the third argument. I think right
now you'd need to use EXECUTE to put it in as if it were the expression to
sort on.