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

On Mon, Aug 1, 2011 at 1:51 PM, Gianpiero Venditti <stratio@hotmail.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