Thread: Ordering a record returned from a stored procedure

Ordering a record returned from a stored procedure

From

"Kent Anderson"

Date:

18 October 2004, 20:00:19

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

Re: Ordering a record returned from a stored procedure

From

Stephan Szabo

Date:

18 October 2004, 20:25:27

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.

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

From

"Kent Anderson"

Date:

18 October 2004, 22:33:06

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

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

From

"Kent Anderson"

Date:

18 October 2004, 23:18:10

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

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

From

Richard Huxton

Date:

19 October 2004, 12:22:05

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