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:
> Karl O. Pinc 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
>>
>>
> 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
>

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