From: Kent Anderson <kenta@ezyield.com>
To: Pgsql-Sql@Postgresql. Org <pgsql-sql@postgresql.org>
Subject: Ordering a record returned from a stored procedure
Date: Mon, 18 Oct 2004 11:59:09 -0400
Message-ID: <LPENJIOOLAIJBFKIBDKOEELLFLAD.kenta@ezyield.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kenta@ezyield.com
Subject: Re: Ordering a record returned from a stored procedure
In-Reply-To: <LPENJIOOLAIJBFKIBDKOEELLFLAD.kenta@ezyield.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox