Postgres Pro
Downloads
Home   >   mailing lists

Thread: Order by in stored functions

Order by in stored functions

From
Andreas Tille
Date:
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.

Re: Order by in stored functions

From
Tom Lane
Date:
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

Re: Order by in stored functions

From
Andreas Tille
Date:
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.

Re: Re: Order by in stored functions

From
Jie Liang
Date:
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