Re: Ordering a record returned from a stored procedure - date issue - Mailing list pgsql-sql

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