Aggregate Random
From PostgreSQL wiki
By Daniel Kahn Gillmor (dkg)
Sometimes, you want a random element from a grouping. This snippet allows you to use RANDOM() as an aggregate function. It should distribute the choices uniformly over each row in the grouping, whether the value selected is NULL or not. (you could modify SFUNC if you wanted to have it select a random non-NULL element)
CREATE OR REPLACE FUNCTION _final_random(anyarray) RETURNS anyelement AS $BODY$ SELECT $1[array_lower($1,1) + floor((1 + array_upper($1, 1) - array_lower($1, 1))*random())]; $BODY$ LANGUAGE 'sql' IMMUTABLE; CREATE AGGREGATE random(anyelement) ( SFUNC=array_append, --Function to call for each row. Just builds the array STYPE=anyarray, FINALFUNC=_final_random, --Function to call after everything has been added to array INITCOND='{}' --Initialize an empty array when starting );
