-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Here's what I have so far:
If you go that route, make sure you check for edge cases, such
as reaching the end of the rows without hitting your number:
while($accum < $r) {
die qq{Ran out of rows!\n} if ! defined $res->{rows}[$i];
Also, your query should be "select i,chance from r1 ORDER BY random()"
else you are getting back the same order each time (until a row is
changed) which certainly reduces the randomness.
Anyway, here's another solution, which shifts as much work as possible
off of the actual random row call, and uses a trigger to keep things
in sync. I switched the 'chance' from 0.25 to 25 (numeric to int) to make
things easier to read.
UPDATE r1 SET chance = chance*100;
ALTER TABLE r1 ALTER COLUMN chance TYPE INTEGER;
CREATE TABLE r2(integer);
CREATE OR REPLACE FUNCTION r1_cleanup() RETURNS trigger LANGUAGE plpgsql AS
$$
DECLARE
mychance integer;
BEGIN
IF TG_OP = 'DELETE' THEN
DELETE FROM r2 WHERE id = OLD.i;
ELSE
IF TG_OP = 'UPDATE' THEN
DELETE FROM r2 WHERE id = OLD.i or id = NEW.i;
END IF;
SELECT chance FROM r1 WHERE i=NEW.i INTO mychance;
LOOP
mychance := mychance - 1;
EXIT WHEN mychance < 0;
INSERT INTO r2 VALUES (NEW.i);
END LOOP;
END IF;
RETURN NULL;
END;
$$;
CREATE TRIGGER r1_trigger AFTER INSERT or UPDATE or DELETE ON r1
FOR EACH ROW EXECUTE PROCEDURE r1_cleanup();
UPDATE r1 SET i=i; -- To initially populate r2
SELECT id FROM r2 ORDER BY random() LIMIT 1; -- repeat as needed
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200502152252
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFCEsOvvJuQZxSWSsgRAjysAJ9X3JpMfuXV2ST049bhCWuJOp6Y1ACg/sNx
PXqxVlfvlsKMTBDDhsh3BmU=
=7/IE
-----END PGP SIGNATURE-----
