From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Kent Anderson <kenta@ezyield.com>
Cc: Pgsql-Sql@Postgresql. Org <pgsql-sql@postgresql.org>
Subject: Re: Ordering a record returned from a stored procedure
Date: Mon, 18 Oct 2004 09:25:11 -0700 (PDT)
Message-ID: <20041018092408.N14426@megazone.bigpanda.com> (raw)
In-Reply-To: <LPENJIOOLAIJBFKIBDKOEELLFLAD.kenta@ezyield.com>
References: <LPENJIOOLAIJBFKIBDKOEELLFLAD.kenta@ezyield.com>
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.
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: sszabo@megazone.bigpanda.com, kenta@ezyield.com
Subject: Re: Ordering a record returned from a stored procedure
In-Reply-To: <20041018092408.N14426@megazone.bigpanda.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