Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2C8629FB21D for ; Sun, 15 Oct 2006 03:16:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 22223-07 for ; Sun, 15 Oct 2006 03:16:43 -0300 (ADT) X-Greylist: delayed 00:05:04.760239 by SQLgrey- Received: from ipmail01.adl2.internode.on.net (ipmail01.adl2.internode.on.net [203.16.214.140]) by postgresql.org (Postfix) with ESMTP id 7187E9FAE4E for ; Sun, 15 Oct 2006 03:16:40 -0300 (ADT) Received: from ppp1-14.lns1.syd7.internode.on.net (HELO [192.168.0.4]) ([59.167.1.14]) by ipmail01.adl2.internode.on.net with ESMTP; 15 Oct 2006 15:41:32 +0930 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AQAAAC1sMUUNjQE X-IronPort-AV: i="4.09,312,1157293800"; d="scan'208"; a="29234364:sNHT33666073" Message-ID: <4531D112.90301@squiz.net> Date: Sun, 15 Oct 2006 16:11:30 +1000 From: Marc McIntyre User-Agent: Thunderbird 1.5.0.7 (Macintosh/20060909) MIME-Version: 1.0 To: Jean-Christophe Roux CC: pgsql-php@postgresql.org Subject: Re: plpgsql returning array References: <20061014202841.17630.qmail@web35304.mail.mud.yahoo.com> In-Reply-To: <20061014202841.17630.qmail@web35304.mail.mud.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.383 tagged_above=0 required=5 tests=SPF_HELO_PASS, SPF_SOFTFAIL X-Spam-Level: * X-Archive-Number: 200610/15 X-Sequence-Number: 3615 You should use a set returning function. Your function can be rewritten as a set returning function like so: CREATE OR REPLACE FUNCTION foo() RETURNS SETOF text AS $BODY$ declare a text; b text; arr text[]; i INTEGER := 0; begin a = 'a'; b = 'b'; arr[0] = a; arr[1] = b; FOR i IN 0..array_upper(arr, 1) LOOP RETURN NEXT arr[i]; END LOOP; end; $BODY$ LANGUAGE 'plpgsql' VOLATILE; And to call the function: marc=# select * from foo(); foo ----- a b (2 rows) Notice that to return a value within the set you use the construct "RETURN NEXT". Set returning functions are generally faster than array returning functions, and are also more useful. For instance you can use set returning functions in sub selects like so: Select * from table where col in (select * from foo()) You can also use a where clause within the select statement ie. marc=# select * from foo() as x where x = 'a'; x --- a (1 row) So a set returning function is like a derived table. In php, the result from a set returning function would be the same as querying a table. Jean-Christophe Roux wrote: > Hello, > in Postgresql, I have a function like this > > CREATE OR REPLACE FUNCTION foo() > RETURNS text[] AS > $BODY$ > declare > a text; > b text; > arr text[]; > begin > a = 'a'; > b = 'b'; > arr[0] = a; > arr[1] = b; > return arr; > end; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > In PHP I would like to read my returned arr. > $message = pg_fetch_result(pg_query("select foo()"),0,0); > print_r($message); > ?> > prints something like: > [0:1]={a,b} > > > I could parse that results, as a string but I'd rather have a nice > array. Any idea how I should change the function and/or the php codes? > Thanks in advance > Jean >