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