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
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 |