Josh Berkus <josh@agliodbs.com> writes:
>> How can I write a function in sql or pl- pgsql that returns a set of
>> tuples ?
> I've had the same question myself for the past month,
This is easy enough in terms of what you do in the function, but it's
not as useful as you might think because of the weird POSTQUEL-derived
syntax for calling such a function.
The key concept is that a table name is also a type name for the struct
type of its rows. So for example,
create table tab1 (f1 int, f2 text);
create function find_tab(int) returns setof tab1 as
'select * from tab1 where f1 = $1'
language 'sql';
insert into tab1 values (1,'one');
insert into tab1 values (1,'another one');
insert into tab1 values (2, 'two');
The problem is that struct types aren't really supported by the
expression evaluator; the only thing you can usefully do with them
is select out one column. So this doesn't work very well:
select find_tab(1); ?column?
------------10743627921074362792
(2 rows)
The obvious way to do the selecting of a column is
select find_tab(1).f2;
ERROR: parser: parse error at or near "."
but for some reason the parser won't accept that. (Perhaps this could
be fixed, I haven't looked into it.) The only way to invoke such a
function at present is to apply it to a column from some other table and
write it as a POSTQUEL dot-function:
select tab2.fld1.find_tab.f2 from tab2;
which is the POSTQUEL notation for what a saner person would call
select find_tab(tab2.fld1).f2 from tab2;
Bizarre syntax aside, the real problem with this whole approach is that
if you've got a function returning tuple(s), you probably want to be
able to get at the tuple elements and do things with them, *without*
re-evaluating the function for each such use. So I think what we really
want to do is to allow functions returning tuple sets to be elements
of a FROM clause:
select f1 + 1, f2 from find_tab(33);
This has been speculated about but no one's looked at what it would take
to make it work.
regards, tom lane