Re: Inserting Multiple Random Rows

Justin (et al),

> You now what might work

In the interest of efficiency, I abandoned the quest for the perfect
query and wrote my own function and used a modified version of
depesz's get_random_id() function:

CREATE OR REPLACE FUNCTION gen_fake_addresses() RETURNS INTEGER AS
$gen_fake_addresses$
DECLARE
v_uid BIGINT;
v_cid INTEGER;
v_cst RECORD;
v_count BIGINT := 0;

BEGIN
FOR v_uid IN
SELECT userid
FROM users
WHERE userid NOT IN (SELECT userid FROM useraddrs)
LOOP
SELECT INTO v_cid get_random_city();
SELECT INTO v_cst cityname, stateabbr FROM cities WHERE cid = v_cid;
INSERT INTO useraddrs(userid, addrdesc, city, stprov)
VALUES (v_uid, 'Home', v_cst.cityname, v_cst.stateabbr);
v_count := v_count + 1;
END LOOP;
RETURN v_count;
END;
$gen_fake_addresses$ LANGUAGE plpgsql VOLATILE;

/* This is depesz's */
CREATE OR REPLACE FUNCTION get_random_city() RETURNS INT4 AS
$get_random_city$
DECLARE
id_range RECORD;
reply INT4;
try INT4 := 0;

BEGIN
SELECT min(cid), max(cid) - min(cid) + 1 AS range INTO id_range FROM cities;
WHILE (try < 10) LOOP
try := try + 1;
reply := FLOOR(RANDOM() * id_range.range) + id_range.min;
PERFORM cid FROM cities WHERE cid = reply;
IF FOUND THEN
RETURN reply;
END IF;
END LOOP;
RAISE EXCEPTION 'No record found in % tries', try;
END;
$get_random_city$ LANGUAGE plpgsql STABLE;

I'd like to thank Justin Graf for his time, effort, and assistance
with this problem.

-- Gary Chambers

/* Nothing fancy and nothing Microsoft! */