pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOrder by in stored functions
4+ messages / 3 participants
[nested] [flat]
* Order by in stored functions
@ 2000-09-04 05:18 Andreas Tille <tillea@rki.de>
2000-09-04 16:10 ` Re: Order by in stored functions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Tille @ 2000-09-04 05:18 UTC (permalink / raw)
To: PostgreSQL SQL <pgsql-sql@hub.org>
Hello
I tried to create the following function
CREATE Function pGetMenu ( int )
returns setof varchar
As '
SELECT IdMenuShow || IdWebPage
FROM Menu
WHERE IdMenu = $1
ORDER BY IdSort ;
' language 'SQL' ;
I've got the following error message:
ERROR: function declared to return varchar returns multiple values in final retrieve
If O just remove the ORDER BY clause als works well so I guess that the
ORDER BY has to be replaced by somethjing other. Could somebody
enlighten me how to do the ordering in a stored procedure?
Kind regards
Andreas.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Order by in stored functions
2000-09-04 05:18 Order by in stored functions Andreas Tille <tillea@rki.de>
@ 2000-09-04 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-09-05 06:26 ` Re: Order by in stored functions Andreas Tille <tillea@rki.de>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2000-09-04 16:10 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>
Andreas Tille <tillea@rki.de> writes:
> I've got the following error message:
> ERROR: function declared to return varchar returns multiple values in
> final retrieve
This is a bug that has already been fixed in current sources: the check
that makes sure your select produces only one varchar column is
mistakenly counting the hidden IdSort column that's needed to sort by.
I don't know of any good workaround in 7.0, short of patching the
erroneous code. Have you thought about using a view, rather than a
function returning set?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Order by in stored functions
2000-09-04 05:18 Order by in stored functions Andreas Tille <tillea@rki.de>
2000-09-04 16:10 ` Re: Order by in stored functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-09-05 06:26 ` Andreas Tille <tillea@rki.de>
2000-09-05 17:53 ` Re: Re: Order by in stored functions Jie Liang <jliang@ipinc.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas Tille @ 2000-09-05 06:26 UTC (permalink / raw)
To: ; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>
On Mon, 4 Sep 2000, Tom Lane wrote:
> This is a bug that has already been fixed in current sources: the check
> that makes sure your select produces only one varchar column is
> mistakenly counting the hidden IdSort column that's needed to sort by.
Is there any patch against 7.0.2 sources which might help me (or the
Debian package maintainer out?
> I don't know of any good workaround in 7.0, short of patching the
> erroneous code. Have you thought about using a view, rather than a
> function returning set?
I could try that.
Is there any general advise for more or less beginners like me regarding
when to use views and when to use functions?
Kind regards
Andreas.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Re: Order by in stored functions
2000-09-04 05:18 Order by in stored functions Andreas Tille <tillea@rki.de>
2000-09-04 16:10 ` Re: Order by in stored functions Tom Lane <tgl@sss.pgh.pa.us>
2000-09-05 06:26 ` Re: Order by in stored functions Andreas Tille <tillea@rki.de>
@ 2000-09-05 17:53 ` Jie Liang <jliang@ipinc.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jie Liang @ 2000-09-05 17:53 UTC (permalink / raw)
To: Andreas Tille <tillea@rki.de>; +Cc: PostgreSQL SQL <pgsql-sql@hub.org>
Hi,
My experience is when you involoved that you have to use some CONTROL
LANGUAGE
such as LOOP, IF ... ELSE ... or value transfer (use variables), then using
function, otherwise
using view, temp table...
In Postgres, function is another way to store procedure.
Andreas Tille wrote:
> On Mon, 4 Sep 2000, Tom Lane wrote:
>
> > This is a bug that has already been fixed in current sources: the check
> > that makes sure your select produces only one varchar column is
> > mistakenly counting the hidden IdSort column that's needed to sort by.
> Is there any patch against 7.0.2 sources which might help me (or the
> Debian package maintainer out?
>
> > I don't know of any good workaround in 7.0, short of patching the
> > erroneous code. Have you thought about using a view, rather than a
> > function returning set?
> I could try that.
>
> Is there any general advise for more or less beginners like me regarding
> when to use views and when to use functions?
>
> Kind regards
>
> Andreas.
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.ipinc.com
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2000-09-05 17:53 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-09-04 05:18 Order by in stored functions Andreas Tille <tillea@rki.de>
2000-09-04 16:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-09-05 06:26 ` Andreas Tille <tillea@rki.de>
2000-09-05 17:53 ` Jie Liang <jliang@ipinc.com>
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