Re: Returning multiple values (but one row) in plpgsql

On 2004.09.08 14:25 Arthur Hoogervorst wrote:
> Hmmm,
>
> Isn't it just easier to make a function which does that and add the
> functions to the SELECT portion of statement instead of adding it to
> the FROM clause?
>
> as in:
> Select invnum, YourFunction(invdet, total), YourFunction(invdet,
> othertotal)
> FROM yourtable

My function is both computationaly intensive and has side effects,
necessitating a single function call.

>
>
> Regards,
>
>
> Arthur
>
>
> On Wed, 8 Sep 2004 13:51:33 -0500, Karl O. Pinc <kop(at)meme(dot)com> wrote:
> > I want to return multiple values, but not a set, only a single row,
> > from a
> > plpgsql function and I can't seem to get it to work. (I suppose I'd
> be
> > happy to return a set, but I can't seem to make that work either.
> > Anyway,
> > what's wrong with this?)
> >
> > Version is:
> >
> > $ rpm -q postgresql
> > postgresql-7.3.4-3.rhl9
> > $ cat /etc/redhat-release Red Hat Linux release 9 (Shrike)
> >
> > Code is:
> >
> > -- Tests for returning multiple values
> >
> > CREATE TYPE returntype AS (a INT, b INT);
> >
> > CREATE FUNCTION return_multiple()
> > RETURNS returntype
> > LANGUAGE plpgsql
> > AS '
> >
> > DECLARE
> > myvar returntype;
> >
> > BEGIN
> > myvar.a := 1;
> > myvar.b := 2;
> >
> > RETURN myvar;
> > END;
> > ';
> >
> > SELECT return_multiple();
> >
> > DROP FUNCTION return_multiple();
> > DROP TYPE returntype CASCADE;
> >
> > Errors are:
> >
> > WARNING: plpgsql: ERROR during compile of return_multiple near line
> 9
> > ERROR: return type mismatch in function returning tuple at or near
> > "myvar"
> >
> > Thanks.
> >
> > Karl <kop(at)meme(dot)com>
> > Free Software: "You don't pay back, you pay forward."
> > -- Robert A. Heinlein
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> > message can get through to the mailing list cleanly
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly
>
>

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein