Date: Wed, 7 Mar 2001 12:50:36 +0100
From: Tomek Zielonka <tomek-lists@mult.i.pl>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: random
Message-ID: <20010307125036.A4207@mult.i.pl>
References: <Pine.NEB.4.10.10103051400050.13137-100000@what.openface.ca>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.2i
In-Reply-To: <Pine.NEB.4.10.10103051400050.13137-100000@what.openface.ca>;
	from jelle@openface.ca on Mon, Mar 05, 2001 at 02:12:15PM -0500

On Mon, Mar 05, 2001 at 02:12:15PM -0500, Jelle Ouwerkerk wrote:
> Hi
> 
> What would be the best way to select a random row from a result set?
> 
> Possibilities:
> 
> 1) o get the total number of rows using count()
>    o generate a random number between 1 and the total
>    o select the n'th row using OFFSET
> 
> 2) o get the total number of rows using count()
>    o generate a random number between 1 and the total
>    o fetch n times

Here's my approach. It allows to get random row from the table, not from result
set in general. Maybe you will be able to modify it to fit your needs.

The table, which we want to get random row from, is not shown here. Let's
assume that it has primary key on integer type column. In the table rnd we keep
references to this table in value column.

Other columns are needed by our selection mechanism. We may want 'values' to be
duplicated (different weights), so there is this 'id' column which uniquely
identifies rnd's row.

CREATE SEQUENCE rnd_seq;

CREATE TABLE rnd (
    id      INT4             NOT NULL DEFAULT NEXTVAL('rnd_seq'),
    r       DOUBLE PRECISION NOT NULL DEFAULT random(),
    value   INT4,
    PRIMARY KEY (id)
);

/* My idea is to index this table with random values.
 * Then it is sufficent to take first row in this order to get a random one
 * and of course we have to modify its r (random) fields not to get it again,
 * and again, ... 
 *
 * I hope you understand my explanation in weak english */
 
CREATE INDEX rnd_r_idx ON rnd (r);

/* This function does it. SELECT it issues is very fast, 'cause it uses an
 * index */

CREATE FUNCTION get_rnd() RETURNS INT4 AS '
    DECLARE
        rowid  INT4;
        val  INT4;
    BEGIN
        SELECT id, value INTO rowid, val
            FROM rnd
            ORDER BY r
            LIMIT 1;
            
        IF NOT FOUND THEN RETURN NULL; END IF;
        
        UPDATE rnd SET r = random() WHERE id = rowid;

        RETURN val;
    END;
' LANGUAGE 'plpgsql';

/* This function only fills the table with test data */

CREATE FUNCTION fill() RETURNS INT4 AS '
    DECLARE
        i     INT4;
    BEGIN
        i := 5555;
        WHILE i <> 0 LOOP
            INSERT INTO rnd (value) VALUES (i);
            i := i - 1;
        END LOOP;
        RETURN 0;
    END;
' LANGUAGE 'plpgsql';

VACUUM ANALYZE rnd;


What do you think? Is it a good idea?

greetings,
Tom

-- 
.signature: Too many levels of symbolic links