Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise

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?

share|improve this question
up vote 6 down vote accepted
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.

share|improve this answer
    
thank you very much, this is helpful – Mo J. Mughrabi Jan 16 '13 at 11:40

Erwin Brandstetter answered the OP's question well enough. However, for others looking for understanding how to randomly pick elements from more complex arrays (like me some two months ago), I expanded his function:

CREATE OR REPLACE FUNCTION random_pick( a anyarray, OUT x anyelement )
  RETURNS anyelement AS
$func$
BEGIN
  IF a = '{}' THEN
    x := NULL::TEXT;
  ELSE
    WHILE x IS NULL LOOP
      x := a[floor(array_lower(a, 1) + (random()*( array_upper(a, 1) -  array_lower(a, 1)+1) ) )::int];
    END LOOP;
  END IF;
END
$func$ LANGUAGE plpgsql VOLATILE RETURNS NULL ON NULL INPUT;

Few assumptions:

  • this is not only for integer arrays, but for arrays of any type

  • we ignore NULL data; NULL is returned only if the array is empty or if NULL is inserted (values of other non-array types produce an error)

  • the array don't need to be formatted as usual - the array index may start and end anywhere, may have gaps etc.

  • this is for one-dimensional arrays

Other notes:

  • without the first IF statement, empty array would lead to an endless loop

  • without the loop, gaps and NULLs would make the function return NULL

  • omit both array_lower calls if you know that your arrays start at zero

  • with gaps in the index, you will need array_upper instead of array_length; without gaps, it's the same (not sure which is faster, but they shouldn't be much different)

  • the +1 after second array_lower serves to get the last value in the array with the same probability as any other; otherwise it would need the random()'s output to be exactly 1, which never happens

  • this is considerably slower than Erwin's solution, and likely to be an overkill for the your needs; in practice, most people would mix an ideal cocktail from the two

share|improve this answer
CREATE FUNCTION random_pick(p_items anyarray)
RETURNS anyelement AS
$$
   SELECT unnest(p_items) ORDER BY RANDOM() LIMIT 1;
$$ LANGUAGE SQL;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.