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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Joel Burton" <jburton(at)scw(dot)org>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Can I create a function that returns many records?
Date: 2000-07-19 18:21:58
Message-ID: 22054.964030918@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

"Joel Burton" <jburton(at)scw(dot)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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Rodger Donaldson 2000-07-19 21:46:43 Re: Database authentication and configuration
Previous Message Joel Burton 2000-07-19 17:13:48 Can I create a function that returns many records?