X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 961A452C97 for ; Mon, 1 Aug 2005 22:48:15 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 33928-09 for ; Tue, 2 Aug 2005 01:48:09 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 810E352C34 for ; Mon, 1 Aug 2005 22:48:09 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j721mCL3014686; Mon, 1 Aug 2005 21:48:12 -0400 (EDT) To: Chris Mungall Cc: pgsql-sql@postgresql.org Subject: Re: Calling SQL functions that return sets In-reply-to: References: <26174.1122940637@sss.pgh.pa.us> Comments: In-reply-to Chris Mungall message dated "Mon, 01 Aug 2005 18:14:22 -0700" Date: Mon, 01 Aug 2005 21:48:12 -0400 Message-ID: <14685.1122947292@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200508/9 X-Sequence-Number: 22237 Chris Mungall writes: > On Mon, 1 Aug 2005, Tom Lane wrote: >> Chris Mungall writes: >>> What are the reasons for deprecating the use of the function in the >>> SELECT clause? >> >> The semantics of having more than one set-returning function in the >> target list are, um, poorly thought out. However, we haven't removed >> the feature because (as you note) there are things you can't do any >> other way. > Is there any roadmap for how this will be handled in future versions? I think it's reasonably safe to say that we won't remove the feature for at least one or two releases after having a 100% substitute (which the present SRF-in-FROM feature is not, as you know). There has been some speculation that the SQL:2003 LATERAL syntax might offer an adequate substitute, but no one is really working on that yet AFAIK. > Coming back to earth, I have a more specific question which follows on > from my initial question. If I have a function 'foo' which takes one > argument and returns a setof some table or composite type, it seems I am > unable to call the function from the select clause. > SELECT foo(1,2); > ERROR: set-valued function called in context that cannot accept a set The present plpgsql implementation only works for SRF-in-FROM. (Which is something that could probably be fixed, but given the development direction we want to go in, it doesn't seem like a very good use of time...) You can do SRF-in-target-list with SQL-language functions or C-coded functions; I'm not certain offhand about the status of the other PL languages. regards, tom lane