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