Re: cursors as table sources

Will Glynn <wglynn(at)freedomhealthcare(dot)org> writes:
> Why can't I SELECT multi_column_function(t.a) FROM some_table t?

You can. At least if you're running a recent release ;-)

regression=# create function foo(int, out f1 int, out f2 int) as $$
regression$# begin
regression$# f1 := $1 + 1;
regression$# f2 := $1 + 2;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select unique1, foo(unique1) from tenk1;
unique1 | foo
---------+-------------
8800 | (8801,8802)
1891 | (1892,1893)
3420 | (3421,3422)
9850 | (9851,9852)
7164 | (7165,7166)
...

> The other option is to make
> multi_column_function actually return a single column in some way that
> the application can split it apart again, but that's really ugly.

That takes a little more hacking, but:

regression=# select unique1,(foo).* from (select unique1, foo(unique1) from tenk1 offset 0) ss;
unique1 | f1 | f2
---------+------+------
8800 | 8801 | 8802
1891 | 1892 | 1893
3420 | 3421 | 3422
9850 | 9851 | 9852
7164 | 7165 | 7166
...

(The OFFSET hack is to ensure the query doesn't get flattened into a
form where foo() will be evaluated multiple times per row.)

regards, tom lane