pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (2+ messages)

Message-ID: <01C0886A.025EEBE0.mdavis@sevainc.com>
Permalink:  ../01C0886A.025EEBE0.mdavis@sevainc.com/
Also on:    postgresql.org/message-id/01C0886A.025EEBE0.mdavis@sevainc.com

 · 

reply

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