Re: Postgres 8.3 Function returning a row with different number of colums

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Gianpiero Venditti <stratio(at)hotmail(dot)it>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Postgres 8.3 Function returning a row with different number of colums
Date: 2011-08-01 19:27:00
Message-ID: CAHyXU0y7Ssv6mhV5gLxFbMZSToipdykJmf8f5KQy925=fp=jAA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, Aug 1, 2011 at 1:51 PM, Gianpiero Venditti <stratio(at)hotmail(dot)it> wrote:
> Hello, I need to write a function that sometimes return a row with only a
> column and sometimes return a row with two columns.
>
> Is it possible to do something like this with record type?
>
> If it's not what's the best alternative to achieve such a result?

you can do this, but it's usually more trouble than worth:
create function test(b bool) returns setof record as
$$
begin
if b then
return query select 1,2;
else
return query select 1,2,3;
end if;
end;
$$ language plpgsql;

postgres=# select * from test(true) V(a int, b int);
a | b
---+---
1 | 2
(1 row)

Time: 26.108 ms
postgres=# select * from test(false) V(a int, b int, c int);
a | b | c
---+---+---
1 | 2 | 3
(1 row)

Doing this is bending the rules of what functions are reasonably
allowed to do and forces some unpleasant syntax outside of the
function call. If it is in any way sane to do so, I'd highly doing
the two argument version always and returning null (or an additional
flag) when you would be wanting to call the one column returning
version. Just because a function returns two columns, there is no
reason why you must inspect or even fetch all the columns they return:

select col1 from func();
select col1, col2 from func();

merlin

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Johnny Edge 2011-08-01 19:47:20 string comparison problem
Previous Message Tom Lane 2011-08-01 19:25:56 Re: Pending trigger events on ALTER TABLE in 8.3