From: Kent Anderson <kenta@ezyield.com>
To: Pgsql-Sql@Postgresql. Org <pgsql-sql@postgresql.org>
Subject: Re: Ordering a record returned from a stored procedure - date issue
Date: Mon, 18 Oct 2004 14:32:12 -0400
Message-ID: <LPENJIOOLAIJBFKIBDKOAEOPFLAD.kenta@ezyield.com> (raw)
In-Reply-To: <20041018092408.N14426@megazone.bigpanda.com>
I have the code working except for the date part of the where clause. Can
anyone point out how
yield_date = ''''10/18/2004''''
can be translated so the 10/18/2004 is coming from a variable?
ie yield_date = '' ... variable with date
Thanks
This works but the date is hardcoded.
FOR result IN
EXECUTE ''SELECT
(..... select all necessary fields ...)
FROM
(.... tables ...)
WHERE hm_key= '' || hmhmkey || ''
AND yield_date = ''''10/18/2004''''
ORDER BY '' || sort
LOOP
RETURN next result;
END LOOP;
RETURN result;
This doesn't work and I am sure its due to all the '''' getting out of hand
when I try to have a date variable used by the string. (sorry for the mess
of apostraphes)
I have tried several variations but keep getting no records returned or an
error.
yield_date = '' || '''' || submissiondate || '''' || '' (returns 0 rows but
no error - the date variable does have a valid date in it)
yield_date = '''' || submissiondate || '''' (returns ERROR: invalid input
syntax for type date: " || submissiondate || ")
yield_date = '' || '''' submissiondate '''' || '' (returns ERROR: syntax
error at or near "$2" at character 982)
yield_date = '' || '' || submissiondate || '' || '' (returns ERROR: column
"submissiondate" does not exist)
FOR result IN
EXECUTE ''SELECT
(..... select all necessary fields ...)
FROM
(.... tables ...)
WHERE hm_key= '' || hmhmkey || ''
AND yield_date = '' || '''' || submissiondate || '''' || ''
ORDER BY '' || sort
LOOP
RETURN next result;
END LOOP;
RETURN result;
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Stephan Szabo
Sent: Monday, October 18, 2004 11:25 AM
To: Kent Anderson
Cc: Pgsql-Sql@Postgresql. Org
Subject: Re: [SQL] 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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
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 - date issue
In-Reply-To: <LPENJIOOLAIJBFKIBDKOAEOPFLAD.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