From kenta@ezyield.com Mon Oct  18 16:00:17 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id C0CE232AA3C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Oct 2004 17:00:17 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 27038-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Oct 2004 16:00:06 +0000 (GMT)
Received: from ezyield.com (unknown [209.164.30.206])
	by svr1.postgresql.org (Postfix) with ESMTP id 6F0B832A9F5
	for <pgsql-sql@postgresql.org>; Mon, 18 Oct 2004 17:00:02 +0100 (BST)
Received: from localhost (xolinux73g2 [127.0.0.1])
	by ezyield.com (8.12.5/8.11.6) with ESMTP id i9IIuWH6024466
	for <pgsql-sql@postgresql.org>; Mon, 18 Oct 2004 11:56:32 -0700
Received: from ezyield.com ([127.0.0.1])
	by localhost (mail.ezyield.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id 23968-05 for <pgsql-sql@postgresql.org>;
	Mon, 18 Oct 2004 11:56:24 -0700 (PDT)
Received: from KENTA (67.108.170.254.ptr.us.xo.net [67.108.170.254])
	by ezyield.com (8.12.5/8.11.6) with SMTP id i9IItUn5024395
	for <pgsql-sql@postgresql.org>; Mon, 18 Oct 2004 11:55:31 -0700
From: "Kent Anderson" <kenta@ezyield.com>
To: "Pgsql-Sql@Postgresql. Org" <pgsql-sql@postgresql.org>
Subject: Ordering a record returned from a stored procedure
Date: Mon, 18 Oct 2004 11:59:09 -0400
Message-ID: <LPENJIOOLAIJBFKIBDKOEELLFLAD.kenta@ezyield.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0010_01C4B509.E0558290"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Virus-Scanned: by amavisd-new 20040701 at ezyield.com
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=2.9 tagged_above=0.0 required=5.0
	tests=FORGED_MUA_OIMO, HTML_50_60, HTML_MESSAGE
X-Spam-Level: **
X-Archive-Number: 200410/112
X-Sequence-Number: 19215

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2800.1400" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D250102715-18102004>I am pull=
ing a=20
report from the database using a stored procedure but cannot get the inform=
ation=20
to return in a specific order unless I hardcode the order by clause.=20
</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D250102715-18102004></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2><SPAN class=3D250102715-18102004>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D250102715-18102004>CREATE OR=
 REPLACE=20
FUNCTION submissionreport(INTEGER, DATE, TEXT) RETURNS setof submissionrec =
AS=20
'<BR>DECLARE<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
result=20
submissionrec%rowtype;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
hmhmkey ALIAS for=20
$1;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;=20
submissiondate ALIAS for=20
$2;<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;=20
sort ALIAS for $3;</SPAN></FONT></DIV>
<DIV><FONT face=3DArial></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D250102715-18102004>BEGIN</SPAN></FONT></DIV>
<DIV><FONT size=3D+0><SPAN class=3D250102715-18102004><FONT face=3DArial si=
ze=3D2>RAISE=20
NOTICE ''The sort order should be: %.'', sort;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>FOR result IN<BR>&nbsp;SELECT</FONT></DIV>
<DIV></SPAN></FONT><SPAN class=3D250102715-18102004><FONT=20
face=3DArial>&nbsp;&nbsp;&nbsp; (..... select all necessary fields=20
...)</FONT></SPAN></DIV>
<DIV><SPAN class=3D250102715-18102004><FONT=20
face=3DArial>FROM&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </FONT></SPAN></DIV>
<DIV><SPAN class=3D250102715-18102004><FONT face=3DArial>&nbsp;&nbsp;&nbsp;=
 (....=20
tables ...)</FONT></SPAN></DIV>
<DIV><SPAN class=3D250102715-18102004><FONT=20
face=3DArial>WHERE&nbsp;&nbsp;&nbsp;</FONT></SPAN></DIV>
<DIV><SPAN class=3D250102715-18102004><FONT face=3DArial>&nbsp;&nbsp;&nbsp;=
 (...=20
contraints)</FONT></SPAN></DIV>
<DIV><SPAN class=3D250102715-18102004><FONT face=3DArial></FONT></SPAN>&nbs=
p;</DIV>
<DIV><SPAN class=3D250102715-18102004><FONT face=3DArial>ORDER BY=20
sort<BR>LOOP<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURN next=20
result;<BR>END LOOP;</FONT></SPAN></DIV>
<DIV><FONT face=3DArial></FONT>&nbsp;</DIV>
<DIV><SPAN class=3D250102715-18102004><FONT face=3DArial>&nbsp;RETURN=20
result;</FONT></SPAN></DIV>
<DIV><FONT face=3DArial></FONT>&nbsp;</DIV><SPAN class=3D250102715-18102004>
<DIV><BR><FONT face=3DArial>END;<BR>' LANGUAGE plpgsql;</FONT></DIV>
<DIV><FONT face=3DArial></FONT>&nbsp;</DIV>
<DIV><SPAN class=3D250102715-18102004><FONT face=3DArial>What am I missing?=
 The=20
returned data&nbsp;is ordered&nbsp;if the "Order By" clause has the values =
hard=20
coded but doesn't seem to read the "sort" variable.</FONT></SPAN></DIV>
<DIV><SPAN class=3D250102715-18102004><FONT face=3DArial></FONT></SPAN>&nbs=
p;</DIV>
<DIV><SPAN class=3D250102715-18102004><FONT face=3DArial>Any help would be=
=20
appreciated.</FONT></SPAN></DIV></SPAN></SPAN></FONT></DIV><SPAN=20
class=3D250102715-18102004>
<DIV><FONT face=3DArial=20
size=3D2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;=20
</FONT></SPAN></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D250102715-18102004></SPAN></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Kent Anderson</FONT></DIV></BODY></HTML>

------=_NextPart_000_0010_01C4B509.E0558290--





From sszabo@megazone.bigpanda.com Mon Oct  18 16:25:24 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 515CF329E0E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Oct 2004 17:25:24 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 35241-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Oct 2004 16:25:15 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 6356E32A3E9
	for <pgsql-sql@postgresql.org>; Mon, 18 Oct 2004 17:25:13 +0100 (BST)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 22CDB35591; Mon, 18 Oct 2004 09:25:11 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 214C2354C4; Mon, 18 Oct 2004 09:25:11 -0700 (PDT)
Date: Mon, 18 Oct 2004 09:25:11 -0700 (PDT)
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
In-Reply-To: <LPENJIOOLAIJBFKIBDKOEELLFLAD.kenta@ezyield.com>
Message-ID: <20041018092408.N14426@megazone.bigpanda.com>
References: <LPENJIOOLAIJBFKIBDKOEELLFLAD.kenta@ezyield.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200410/113
X-Sequence-Number: 19216


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.




From kenta@ezyield.com Mon Oct  18 18:33:04 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id EC7C532A0AE
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Oct 2004 19:33:04 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 78257-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Oct 2004 18:33:01 +0000 (GMT)
Received: from ezyield.com (unknown [209.164.30.206])
	by svr1.postgresql.org (Postfix) with ESMTP id 7996332A00B
	for <pgsql-sql@postgresql.org>; Mon, 18 Oct 2004 19:32:59 +0100 (BST)
Received: from localhost (xolinux73g2 [127.0.0.1])
	by ezyield.com (8.12.5/8.11.6) with ESMTP id i9ILTWH6003441
	for <pgsql-sql@postgresql.org>; Mon, 18 Oct 2004 14:29:32 -0700
Received: from ezyield.com ([127.0.0.1])
	by localhost (mail.ezyield.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id 02880-07 for <pgsql-sql@postgresql.org>;
	Mon, 18 Oct 2004 14:29:24 -0700 (PDT)
Received: from KENTA (67.108.170.254.ptr.us.xo.net [67.108.170.254])
	by ezyield.com (8.12.5/8.11.6) with SMTP id i9ILSXn5003386
	for <pgsql-sql@postgresql.org>; Mon, 18 Oct 2004 14:28:35 -0700
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>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
In-Reply-To: <20041018092408.N14426@megazone.bigpanda.com>
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Virus-Scanned: by amavisd-new 20040701 at ezyield.com
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No,
 hits=2.7 tagged_above=0.0 required=5.0 tests=FORGED_MUA_OIMO
X-Spam-Level: **
X-Archive-Number: 200410/117
X-Sequence-Number: 19220

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






From kenta@ezyield.com Mon Oct  18 19:18:08 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id F1FF332A195
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Oct 2004 20:18:08 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 89588-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 18 Oct 2004 19:18:00 +0000 (GMT)
Received: from ezyield.com (unknown [209.164.30.206])
	by svr1.postgresql.org (Postfix) with ESMTP id 7893632A1C5
	for <pgsql-sql@postgresql.org>; Mon, 18 Oct 2004 20:17:59 +0100 (BST)
Received: from localhost (xolinux73g2 [127.0.0.1])
	by ezyield.com (8.12.5/8.11.6) with ESMTP id i9IMEWH6006958
	for <pgsql-sql@postgresql.org>; Mon, 18 Oct 2004 15:14:32 -0700
Received: from ezyield.com ([127.0.0.1])
	by localhost (mail.ezyield.com [127.0.0.1]) (amavisd-new, port 10024)
	with ESMTP id 06284-09 for <pgsql-sql@postgresql.org>;
	Mon, 18 Oct 2004 15:14:24 -0700 (PDT)
Received: from KENTA (67.108.170.254.ptr.us.xo.net [67.108.170.254])
	by ezyield.com (8.12.5/8.11.6) with SMTP id i9IMDZn5006899
	for <pgsql-sql@postgresql.org>; Mon, 18 Oct 2004 15:13:37 -0700
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 15:17:14 -0400
Message-ID: <LPENJIOOLAIJBFKIBDKOIEAAFMAD.kenta@ezyield.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
In-Reply-To: <LPENJIOOLAIJBFKIBDKOAEOPFLAD.kenta@ezyield.com>
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
X-Virus-Scanned: by amavisd-new 20040701 at ezyield.com
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No,
 hits=2.7 tagged_above=0.0 required=5.0 tests=FORGED_MUA_OIMO
X-Spam-Level: **
X-Archive-Number: 200410/118
X-Sequence-Number: 19221

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






From dev@archonet.com Tue Oct  19 08:22:01 2004
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id F0AA7329ECA
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Oct 2004 09:22:01 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 00858-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 19 Oct 2004 08:21:51 +0000 (GMT)
Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net
	[194.217.242.88])
	by svr1.postgresql.org (Postfix) with ESMTP id E9EA2329ED3
	for <pgsql-sql@postgresql.org>; Tue, 19 Oct 2004 09:21:52 +0100 (BST)
Received: from mwynhau.demon.co.uk ([193.237.186.96]
 helo=mainbox.archonet.com)
	by anchor-post-30.mail.demon.net with esmtp (Exim 4.42)
	id 1CJpFb-0001Ms-13; Tue, 19 Oct 2004 08:21:51 +0000
Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP
	id 13FE71637E; Tue, 19 Oct 2004 09:21:29 +0100 (BST)
Message-ID: <4174CE8C.6090707@archonet.com>
Date: Tue, 19 Oct 2004 09:21:32 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 0.7 (X11/20040615)
X-Accept-Language: en-us, en
MIME-Version: 1.0
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
References: <LPENJIOOLAIJBFKIBDKOIEAAFMAD.kenta@ezyield.com>
In-Reply-To: <LPENJIOOLAIJBFKIBDKOIEAAFMAD.kenta@ezyield.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200410/122
X-Sequence-Number: 19225

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