pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan I create a function that returns many records?
2+ messages / 2 participants
[nested] [flat]
* Can I create a function that returns many records?
@ 2000-07-19 17:13 Joel Burton <jburton@scw.org>
0 siblings, 1 reply; 2+ messages in thread
From: Joel Burton @ 2000-07-19 17:13 UTC (permalink / raw)
To: pgsql-sql
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)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Can I create a function that returns many records?
@ 2000-07-19 18:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joel Burton <jburton@scw.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2000-07-19 18:21 UTC (permalink / raw)
To: Joel Burton <jburton@scw.org>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-07-19 18:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-07-19 17:13 Can I create a function that returns many records? Joel Burton <jburton@scw.org>
2000-07-19 18:21 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox