pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedlooping through results of a SELECT
2+ messages / 2 participants
[nested] [flat]
* looping through results of a SELECT
@ 2001-01-27 19:15 Nagy Tamas <nthomas@cs.bme.hu>
0 siblings, 0 replies; 2+ messages in thread
From: Nagy Tamas @ 2001-01-27 19:15 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* RE: looping through results of a SELECT
@ 2001-01-27 21:04 Michael Davis <mdavis@sevainc.com>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Davis @ 2001-01-27 21:04 UTC (permalink / raw)
To: 'Nagy Tamas' <nthomas@cs.bme.hu>; pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-01-27 21:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-27 19:15 looping through results of a SELECT Nagy Tamas <nthomas@cs.bme.hu>
2001-01-27 21:04 RE: looping through results of a SELECT Michael Davis <mdavis@sevainc.com>
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