DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=narLm2yyrxI1Vq03hDHD5/1rxzTh5UlAizQH4wGhH8ccyMzZKDBWNqDMynbF5j9TEw
	E83oyjuYRVwofTckX7A32pye+YvxdUDeby5tDiS1Ftlu4o+Mz85OIPWeP3uR8IEGepJj
	lsK+g287cVSQseAzI+UT20K4S3/A8Jk6E4iXc=
MIME-Version: 1.0
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>
Date: Wed, 28 Apr 2010 16:04:11 -0400
Message-ID: <h2v302670f21004281304l5d4dd567s680b1ccdc49211f9@mail.gmail.com>
Subject: Re: Inserting Multiple Random Rows
From: Gary Chambers <gwchamb@gmail.com>
To: Justin Graf <justin@magwerks.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1

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! */