Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e6JIM1h64500 for ; Wed, 19 Jul 2000 14:22:01 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id OAA22057; Wed, 19 Jul 2000 14:21:59 -0400 (EDT) To: "Joel Burton" cc: pgsql-sql@postgresql.org Subject: Re: Can I create a function that returns many records? In-reply-to: <3975A98C.26157.984840B@localhost> References: <3975A98C.26157.984840B@localhost> Comments: In-reply-to "Joel Burton" message dated "Wed, 19 Jul 2000 13:13:48 -0400" Date: Wed, 19 Jul 2000 14:21:58 -0400 Message-ID: <22054.964030918@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200007/183 "Joel Burton" 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