Thread: Function that returns a tuple.

How can I write a function in sql or pl- pgsql that returns a set of
tuples ?
                                                               Thanks.

Claudio

Claudio,

> 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, which has gone
unanswered (hint, hint, Jeff!).  Currently I'm using custom functions to
build views and then referenceing the views:

CREATE FUNCTION fun_generate_statistics (do a bunch of stuff, ending with:CREATE VIEW vw_generate_statistics (    bunch
ofstuff););
 

Then the front-end code references vw_generate_statistics.

Not elegant, but sufficient until I hear from Jeff or Jan Wieck.
                -Josh Berkus

-- 
______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology      josh@agliodbs.com   and data management solutions       (415) 436-9166  for law firms, small
businesses      fax  436-0137   and non-profit organizations.       pager 338-4078                               San
Francisco

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