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
SELECTINTO v_cid get_random_city(); SELECT INTO v_cst cityname, stateabbr FROM cities WHERE cid = v_cid;
INSERTINTO 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! */
