PostgreSQL PL/pgSQL random value from array of values

Question

How can I declare an array like variable with two or three values and get them randomly during execution?

a := [1, 2, 5] -- sample sake
select random(a) -- returns random value

Any suggestion where to start?

Answer 1

Try this one:

select (array['Yes', 'No', 'Maybe'])[floor(random() * 3 + 1)];

Answer 2

CREATE FUNCTION random_pick()
  RETURNS int AS
$func$
DECLARE
   a int[] := '{[0:2]=1,2,5}'; -- sample sake
BEGIN
   RETURN a[floor((random()*3))::int];
END
$func$ LANGUAGE plpgsql VOLATILE

random() returns a value x where 0.0 <= x < 1.0. Multiply by three and floor() it to get 0, 1 or 2 with equal chance. This would be off-by-one for the default array index that starts with 1. For efficiency, I declare the array index to start with 0 instead.

The manual on these mathematical functions.