Return random records in Postgres

Lately I've been experimenting with building a simple "twitter" app in Postgres by pushing as much logic in the database layer as possible. This includes data validation/sanitization, triggers, and functions.

I needed a way to seed the database with sample data which sometimes required me to specify foreign keys to existing records. I ended up writing a couple plpgsql functions to make this easier.

CREATE FUNCTION random.record(table_name text, exclude uuid DEFAULT uuid_generate_v4())  
  RETURNS record AS $$
    DECLARE
      record record;
    BEGIN
      EXECUTE 'SELECT * FROM ' || table_name || ' WHERE id != $1 ORDER BY random() LIMIT 1'
      INTO record
      USING exclude;

      RETURN record;
    END;
  $$ LANGUAGE plpgsql VOLATILE;

The function above returns a random record from any specied table. It can be called with something like SELECT random.record('users').

It optionally accepts an id to exclude in case you're running a query like:

INSERT INTO followers (follower_id, user_id)  
SELECT id as follower_id, random.id('users', id) as user_id  
FROM users;  

Use this other function if you only need the id: SELECT random.id('users').

CREATE FUNCTION random.id(table_name text, exclude uuid DEFAULT uuid_generate_v4())  
  RETURNS uuid AS $$
    DECLARE
      record record;
    BEGIN
      record := random.record(table_name, exclude);
      RETURN record.id;
    END;
  $$ LANGUAGE plpgsql VOLATILE;

These functions expect your table's primary key to be a uuid called id. You may need to tweak them to match your specific schema if you're using integer or some other primary key column name.

Check out how to use uuids in PostgreSQL if you're not already!