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
