Remembering values in pl/pgsql

From: Robert Fitzpatrick <robert(at)webtent(dot)com>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: Remembering values in pl/pgsql
Date: 2004-06-16 16:14:29
Message-ID: 1087402469.26084.11.camel@columbus
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Running 7.4.2, I have a pl/pgsql function with a WHILE LOOP that inserts
records a set number of times. Each time, the function generates a
random number and INSERT INTO a table, if that number has been used
already, I need to enter all fields leaving the incremented number NULL.
How can I keep track of the numbers already used, does it support arrays
and them? I tried a SELECT INTO (shown below), but I guess the INSERT
does not commit until the LOOP is finished. Is there a way to commit the
record before the next item in the LOOP?

WHILE sampleno <= units_to_test LOOP
randno := random();
randresult := bldginfo.units_count*randno;
randround := CEIL(randresult);
SELECT INTO checkit COUNT(public.tblhud74b.similar_group_id) AS
unit_count FROM public.tblhud74b WHERE
(public.tblhud74b.similar_group_id = bldginfo.similar_group_id) AND
(public.tblhud74b.rounded = randround);
IF NOT FOUND THEN
INSERT INTO tblhud74b VALUES (bldginfo.similar_group_id,
bldginfo.units_count, randno, randresult, randround, sampleno);
sampleno := sampleno + 1;
nounits := nounits + 1;
ELSE
INSERT INTO tblhud74b VALUES (bldginfo.similar_group_id,
bldginfo.units_count, randno, randresult, randround);
END IF;
END LOOP;

--
Robert

Browse pgsql-general by date

  From Date Subject
Next Message Csaba Nagy 2004-06-16 16:23:30 Re: Tracking down deadlocks
Previous Message Tom Lane 2004-06-16 16:03:39 Re: Tracking down deadlocks