From: Michael Davis <mdavis@sevainc.com>
To: 'Nagy Tamas' <nthomas@cs.bme.hu>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: RE: looping through results of a SELECT
Date: Sat, 27 Jan 2001 14:04:00 -0700
Message-ID: <01C0886A.025EEBE0.mdavis@sevainc.com> (raw)
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'';
return 0;
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: mdavis@sevainc.com, nthomas@cs.bme.hu
Subject: Re: looping through results of a SELECT
In-Reply-To: <01C0886A.025EEBE0.mdavis@sevainc.com>
References: <01C0886A.025EEBE0.mdavis@sevainc.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox