RE: looping through results of a SELECT

Try using aggregate functions.  Creating your own aggregate function is 
fairly easy and can produce the exact results you are looking for.   In 
case this is not good enough, here is an example of some code I used to 
loop through rows in a table in pl/pgsql

CREATE FUNCTION pending_post_transaction(int) RETURNS int AS '  DECLARE     pMemberID ALIAS for $1;     pl
record;    rc        int;  BEGIN     if (pMemberID is null) then        RAISE NOTICE ''MemberID is null'';
return0;     end if;     select count(*) into rc from Pending P where P.MemberID = pMemberID;     if (rc > 0) then
else       RAISE NOTICE ''No rows to process'';        return 0;     end if;     FOR pl IN select * from Pending where
MemberID= pMemberID LOOP        if (pl.InventoryID > 0) then  -- InventoryID is a column in the 
 
Pending table            ...        else            RAISE NOTICE "The InventoryID is empty, skipping the update";
    return 0;        end if;        if (pl.SpecialArrangement >= 0) then -- SpecialArrangement is also 
 
a column in the Pending table            ...        end if;     END LOOP;     return rc;  END;'  LANGUAGE 'plpgsql';


-----Original Message-----
From:    Nagy Tamas [SMTP:nthomas@cs.bme.hu]
Sent:    Saturday, January 27, 2001 12:16 PM
To:    pgsql-sql@postgresql.org
Subject:    looping through results of a SELECT

Hello!
I'd like to write a function, that makes some calculations
(perhaps applies another function) on every row of a result set returned
by a SELECT query. I thought writing a WHILE loop would work, but I
couldn't assign the individual rows to a variable. Then, I read about the
FETCH command, and I tried to use that, but it dies with 'error
near CURSOR' error when I try to use the function. I wrote a PL/PGSQL
function, obviously. So, my question is: is there an easy way to assign
the individual rows of a SELECT result to a variable in a function?
Thanks in advance,
Thomas Nagy