From: Gary Chambers <gwchamb@gmail.com>
To: Justin Graf <justin@magwerks.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Inserting Multiple Random Rows
Date: Wed, 28 Apr 2010 16:04:11 -0400
Message-ID: <h2v302670f21004281304l5d4dd567s680b1ccdc49211f9@mail.gmail.com> (raw)
In-Reply-To: <4BD88686.7070005@magwerks.com>
References: <q2x302670f21004280922ic25b4f91k968effc6926a6243@mail.gmail.com>
<20335.1272474358@sss.pgh.pa.us>
<z2i302670f21004281048n3ed3d9aew9203d8cceec79f74@mail.gmail.com>
<4BD88686.7070005@magwerks.com>
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! */
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gwchamb@gmail.com, justin@magwerks.com
Subject: Re: Inserting Multiple Random Rows
In-Reply-To: <h2v302670f21004281304l5d4dd567s680b1ccdc49211f9@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox