Search Postgresql Archives

Re: random record from small set

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



-----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@xxxxxxxxxxxx
PGP Key: 0x14964AC8 200502152252
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
 
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFCEsOvvJuQZxSWSsgRAjysAJ9X3JpMfuXV2ST049bhCWuJOp6Y1ACg/sNx
PXqxVlfvlsKMTBDDhsh3BmU=
=7/IE
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux