Ordering a record returned from a stored procedure

From: "Kent Anderson" <kenta(at)ezyield(dot)com>
To: "Pgsql-Sql(at)Postgresql(dot) Org" <pgsql-sql(at)postgresql(dot)org>
Subject: Ordering a record returned from a stored procedure
Date: 2004-10-18 15:59:09
Message-ID: LPENJIOOLAIJBFKIBDKOEELLFLAD.kenta@ezyield.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Bruno Wolff III 2004-10-18 16:05:41 Re: Aggregate Function with Argument
Previous Message David Siegal 2004-10-18 15:24:33 Aggregate Function with Argument