Ordering a record returned from a stored procedure

This is a multi-part message in MIME format.

------=_NextPart_000_0010_01C4B509.E0558290
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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

------=_NextPart_000_0010_01C4B509.E0558290
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Re: 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

Re: Ordering a record returned from a stored procedure - date issue

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




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Ordering a record returned from a stored procedure - date issue

Never mind, it requires '''''''' on each side of the variable.

Thanks

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Kent Anderson
Sent: Monday, October 18, 2004 1:32 PM
To: Pgsql-Sql@Postgresql. Org
Subject: Re: [SQL] Ordering a record returned from a stored procedure -
date issue


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




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Ordering a record returned from a stored procedure - date

Kent Anderson wrote:
> Never mind, it requires '''''''' on each side of the variable.

You will be delighted to learn that "dollar quoting" is in 8.0, which
allows you to do things like:

CREATE FUNCTION .... AS $$
...function body here without needing doubling of '
$$
LANGUAGE plpgsql;

I believe you can nest them too so long as you change the included
string: $UNIQUESYMBOL1$

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Kent Anderson
> Sent: Monday, October 18, 2004 1:32 PM
> To: Pgsql-Sql@Postgresql. Org
> Subject: Re: [SQL] Ordering a record returned from a stored procedure -
> date issue
>
>
> 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

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly