Returning multiple values (but one row) in plpgsql

[Karl O. Pinc]

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@meme.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@postg resql.org so that your
message can get through to the mailing list cleanly

[Ron St-Pierre]

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

Karl O. Pinc wrote:
[color=blue]
> 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@meme.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@postg resql.org so that your
> message can get through to the mailing list cleanly
>
>[/color]
It works with 7.4.3, except the SELECT statement is
testdb=# SELECT * FROM return_multiple ();
a | b
---+---
1 | 2
(1 row)

Ron


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Arthur Hoogervorst]

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

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(in vdet, total), YourFunction(in vdet, othertotal)
FROM yourtable



Regards,


Arthur


On Wed, 8 Sep 2004 13:51:33 -0500, Karl O. Pinc <kop@meme.com > wrote:[color=blue]
> 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@meme.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@postg resql.org so that your
> message can get through to the mailing list cleanly
>[/color]

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Karl O. Pinc]

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


On 2004.09.08 14:25 Arthur Hoogervorst wrote:[color=blue]
> 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(in vdet, total), YourFunction(in vdet,
> othertotal)
> FROM yourtable[/color]

My function is both computationaly intensive and has side effects,
necessitating a single function call.
[color=blue]
>
>
> Regards,
>
>
> Arthur
>
>
> On Wed, 8 Sep 2004 13:51:33 -0500, Karl O. Pinc <kop@meme.com > wrote:[color=green]
> > 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[/color]
> be[color=green]
> > 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[/color]
> 9[color=green]
> > ERROR: return type mismatch in function returning tuple at or near
> > "myvar"
> >
> > Thanks.
> >
> > Karl <kop@meme.com >
> > Free Software: "You don't pay back, you pay forward."
> > -- Robert A. Heinlein
> >
> > ---------------------------(end of[/color]
> broadcast)---------------------------[color=green]
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postg resql.org so that[/color]
> your[color=green]
> > message can get through to the mailing list cleanly
> >[/color]
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postg resql.org so that
> your
> message can get through to the mailing list cleanly
>
>[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Karl O. Pinc]

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

Poo. Looks like my postgresql has a bug. :-(

The only work-around I can think of is to return
a bunch of values in a delimited string and then
parse them out upon return. :-(

Anybody got a better idea? Anybody know just when
this was fixed? If I knew I might be able to see about
getting our site upgraded. (I tried looking in the CVS
web interface, but was quickly daunted by the number of
source files.)

On 2004.09.08 14:07 Ron St-Pierre wrote:[color=blue]
> Karl O. Pinc wrote:
>[color=green]
>> 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@meme.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@postg resql.org so that
>> your
>> message can get through to the mailing list cleanly
>>
>>[/color]
> It works with 7.4.3, except the SELECT statement is
> testdb=# SELECT * FROM return_multiple ();
> a | b
> ---+---
> 1 | 2
> (1 row)
>
> Ron
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Joe Conway]

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

Karl O. Pinc wrote:[color=blue]
> Anybody got a better idea? Anybody know just when
> this was fixed? If I knew I might be able to see about
> getting our site upgraded. (I tried looking in the CVS
> web interface, but was quickly daunted by the number of
> source files.)[/color]

select version();
version
-------------------------------------------------------------------------
PostgreSQL 7.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)
(1 row)

CREATE TYPE returntype AS (a INT, b INT);
CREATE OR REPLACE FUNCTION return_multiple ()
RETURNS setof returntype
LANGUAGE plpgsql
AS '
DECLARE
myvar returntype%rowt ype;
BEGIN
myvar.a := 1;
myvar.b := 2;
RETURN NEXT myvar;
RETURN;
END;
';
SELECT * FROM return_multiple ();
a | b
---+---
1 | 2
(1 row)

HTH,

Joe

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Karl O. Pinc]

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

Thanks! I was not declaring the variable %rowtype.
Adding that fixed the problem.

On 2004.09.08 15:46 Joe Conway wrote:[color=blue]
> Karl O. Pinc wrote:[color=green]
>> Anybody got a better idea? Anybody know just when
>> this was fixed? If I knew I might be able to see about
>> getting our site upgraded. (I tried looking in the CVS
>> web interface, but was quickly daunted by the number of
>> source files.)[/color]
>
> select version();
> version
> -------------------------------------------------------------------------
> PostgreSQL 7.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc
> (GCC) 3.3.3 20040412 (Red Hat Linux 3.3.3-7)
> (1 row)
>
> CREATE TYPE returntype AS (a INT, b INT);
> CREATE OR REPLACE FUNCTION return_multiple ()
> RETURNS setof returntype
> LANGUAGE plpgsql
> AS '
> DECLARE
> myvar returntype%rowt ype;
> BEGIN
> myvar.a := 1;
> myvar.b := 2;
> RETURN NEXT myvar;
> RETURN;
> END;
> ';
> SELECT * FROM return_multiple ();
> a | b
> ---+---
> 1 | 2
> (1 row)
>
> HTH,
>
> Joe[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Joe Conway]

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

Karl O. Pinc wrote:[color=blue]
> Thanks! I was not declaring the variable %rowtype.
> Adding that fixed the problem.
>[/color]

Ah yes, that works too. For the record:

CREATE TYPE returntype AS (a INT, b INT);
CREATE OR REPLACE FUNCTION return_multiple ()
RETURNS returntype
LANGUAGE plpgsql
AS '
DECLARE
myvar returntype%rowt ype;
BEGIN
myvar.a := 1;
myvar.b := 2;
RETURN myvar;
END;
';
SELECT * FROM return_multiple ();
a | b
---+---
1 | 2
(1 row)

Joe

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Greg Stark]

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


"Karl O. Pinc" <kop@meme.com > writes:
[color=blue]
> Anybody got a better idea?[/color]

If they're all the same data type you could alternatively use an array. Which
is more convenient might depend on how much you want to throw around the
composite data type in intermediate code before peeking at the elements.

--
greg


---------------------------(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

[Karl O. Pinc]

Referencing multiple values returned to a plpgsql function - Was: Re: Returning multiple values (but one row) in plpgsql

Once I've gotten multiple values back from a plpgsql function,
how do I actually reference those values in another plpgsql
function? I've tried several syntaxes and keep getting errors.

Various attempts are below.

Thanks.

On 2004.09.08 15:59 Joe Conway wrote:
[color=blue]
>
> Ah yes, that works too. For the record:
>
> CREATE TYPE returntype AS (a INT, b INT);
> CREATE OR REPLACE FUNCTION return_multiple ()
> RETURNS returntype
> LANGUAGE plpgsql
> AS '
> DECLARE
> myvar returntype%rowt ype;
> BEGIN
> myvar.a := 1;
> myvar.b := 2;
> RETURN myvar;
> END;
> ';
> SELECT * FROM return_multiple ();
> a | b
> ---+---
> 1 | 2
> (1 row)[/color]

PostgreSQL 7.3.4 on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)


CREATE FUNCTION return_multiple 2()
RETURNS returntype
LANGUAGE plpgsql
AS '

DECLARE
myvar returntype%rowt ype;
a INT;
b INT;

BEGIN

-- SELECT INTO a, b FROM return_multiple ();
SELECT INTO a, b return_multiple ();
myvar.a := a;
myvar.b := b;

-- SELECT INTO myvar return_multiple ();

RETURN myvar;
END;
';


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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)