Thread: Can I create a function that returns many records?

I'd like to create a function that outs the results of a select query. 
It might work like this:

SELECT METAPHONE('jennifer foobar');
persid | name
-----------------------------     1 | jennifer fubar    10 | gennifer foobar

[I already have the metaphone comparing working, it's the returning 
the SELECt that needs help.]

Working through the documentation, I see examples using CREATE 
FUNCTION METAPHONE(text) RETURNS setof tblPerson AS 'SELECT 
* FROM tblPerson' LANGUAGE 'sql',  but this returns only a single 
numeric value (that doesn't seem to correspond to anything.)

Is there a way to do this? Any help would be very appreciated.



--
Joel Burton, Director of Information Systems -*- jburton@scw.org
Support Center of Washington (www.scw.org)

"Joel Burton" <jburton@scw.org> writes:
> Working through the documentation, I see examples using CREATE 
> FUNCTION METAPHONE(text) RETURNS setof tblPerson AS 'SELECT 
> * FROM tblPerson' LANGUAGE 'sql',  but this returns only a single 
> numeric value (that doesn't seem to correspond to anything.)

IIRC, you can build a function that returns a SETOF any simple datatype,
but the support for returning tuples is suffering from bit-rot.
(I imagine it worked, more or less, back in Berkeley days.)  If you can
live with returning one column at a time, you might be OK.

SELECT function-returning-set is a pretty peculiar notation anyway,
since SQL doesn't really have any concept of set data types in
expressions.  What's more likely to be supported someday is a
function-returning-set used as a source table in a SELECT's
from-clause, ie,
SELECT blah blah blah FROM function-returning-set(arguments) ...

This doesn't require any creative interpretation of SQL semantics
to figure out what to do with the multiple result rows.

BTW, is there a good reason why you don't just use a VIEW?
        regards, tom lane