Message-ID: <3E31EB8F.6040606@incentre.net>
Date: Fri, 24 Jan 2003 18:42:39 -0700
From: Guy Fraser <guy@incentre.net>
Organization: J I Reasearch
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20021003
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Getting multiple rows in plpgsql function
References: <42509.216.86.192.34.1043437147.squirrel@www.la-rubber.com>
	<20030124223626.GB24536@cc.usu.edu>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

NOTE: This is a feature in 7.3 it was either added or fixed, so you will not 
be able to do this unless you are using version 7.3. Remember to backup with 
pg_dumpall before you upgrade.

This is a sample sent to me earlier this week, that iterates an integer array:

########Cut Here########
CREATE TYPE group_view AS (grosysid int4, groname name, usesysid int4, usename 
name);

CREATE OR REPLACE FUNCTION expand_groups() RETURNS SETOF group_view AS '
DECLARE
   rec record;
   groview record;
   low int;
   high int;
BEGIN
   FOR rec IN SELECT grosysid FROM pg_group LOOP
     SELECT INTO low
       replace(split_part(array_dims(grolist),'':'',1),''['','''')::int
       FROM pg_group WHERE grosysid = rec.grosysid;
     IF low IS NULL THEN
       low := 1;
       high := 1;
     ELSE
       SELECT INTO high
         replace(split_part(array_dims(grolist),'':'',2),'']'','''')::int
         FROM pg_group WHERE grosysid = rec.grosysid;
       IF high IS NULL THEN
         high := 1;
       END IF;
     END IF;

     FOR i IN low..high LOOP
       SELECT INTO groview g.grosysid, g.groname, s.usesysid, s.usename
         FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i]
         WHERE grosysid = rec.grosysid;
       RETURN NEXT groview;
     END LOOP;
   END LOOP;
   RETURN;
END;
' LANGUAGE 'plpgsql' WITH ( iscachable, isstrict );

CREATE VIEW groupview AS SELECT * FROM expand_groups();
########Cut Here########

One of the tricks is that you apparently need to use the CREATE TYPE commands 
to define the returned result. The veiw at the end just makes queries look 
like a table is being queried rather than a function.

I hope this helps.

Roberto Mello wrote:
> On Fri, Jan 24, 2003 at 11:39:07AM -0800, David Durst wrote:
> 
>>I am wondering how you would handle a select that returns multiple rows
>>in a plpgsql function?
>>
>>In other words lets say I wanted to iterate through the results in
>>the function.
> 
> 
> There are examples in the PL/pgSQL documentation that show you how to do it.
> 
> -Roberto
>