How to use the "setof" of CREATE FUNCTION

Fabien Thiriet <fabien@freever.com> writes:

CREATE FUNCTION foo(varchar) RETURNS setof myTable
AS 'UPDATE .......;
INSERT.......;
SELECT myTable.field2 from myTable'
LANGUAGE 'sql';

I always get an error saying that there is a type mismatch between what is
behing the "setof" and what is return by this function (myTable.field2)

Well, yeah: you declared the function to return a set of the tuple
datatype myTable, not a set of whatever field2's datatype is.
Perhaps you wanted

CREATE FUNCTION foo(varchar) RETURNS setof myTable
AS 'UPDATE .......;
INSERT.......;
SELECT * from myTable'
LANGUAGE 'sql';

which hands back the entire table. Alternatively, if you do want to
return just the one column, you should declare the function to return
setof whatever-type-field2-is.

Note that functions returning sets are not as useful as they should be,
because you can only call them in limited places (at the top level of
a SELECT-list item, IIRC). Functions returning tuples are not as
useful as they should be either, because you can't do anything with
the result except select out an individual column; worse, there's this
bizarre syntax for it --- you can't write the obvious foo(x).bar,
for some reason, but have to do x.foo.bar, which only works for simple
field-of-a-relation arguments. Ugh. This whole area needs work.

regards, tom lane