Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0N6wL016637 for ; Tue, 23 Jan 2001 01:58:21 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0K4TL084621 for ; Fri, 19 Jan 2001 23:29:21 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f0K4PCq86266 for pgsql-sql@postgresql.org; Fri, 19 Jan 2001 23:25:12 -0500 (EST) (envelope-from news) From: Ian Harding X-Newsgroups: comp.databases.postgresql.sql Subject: Re: pl/pgsql Limits Date: Fri, 19 Jan 2001 20:31:09 -0800 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 34 Message-ID: <3A69148D.F0DFF8D@pakrat.com> References: <2300.979407081@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org X-Mailer: Mozilla 4.61 [en] (X11; U; Linux 2.2.12-20 i686) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200101/283 X-Sequence-Number: 952 Tom Lane wrote: > Jan Wieck writes: > >> As it is known that any funtion, written in pl/pgsql, can only > >> retrun one tuple. I am just wondering it were true as well for function > >> written in C language. I need to write few function that will retrun > >> mulitiple rows satsifying a certain set of conditions. > > > We plan to tackle the problem for v7.2. > > It is possible for a C function to return a set (ie, multiple values > returned over successive calls) as of 7.1; it's even documented, see > src/backend/utils/fmgr/README. And you can return a tuple if you know > how (this part is not documented, but you can crib it from the > SQL-function support in backend/executor/functions.c). > > The real problem is that the rest of the system doesn't let you *do* > anything very useful with either set-valued or tuple-valued functions. > This is what we need to address in future releases. Ideally I think > a function returning sets and/or tuples should be treated as a table > source, so that you'd write ... FROM function(args) AS alias, ... > > regards, tom lane How do you work around this? All I can think of is to call a the function and have it create a temporary table, then select from that table in the same transaction (to use the same connection). Does a temporary table created by a function continue to exist after the function finishes? I have a stored procedure in SQL Server that I have to create a temporary table then select from the table inside the same procedure. I'm not sure it would work after the SP is done... Ian