PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Mimicing CHOOSE() function?

Good afternoon! Looking for some advice specific to Postgres. It does not have an equivalent to CHOOSE(), but I would like to use it to pick a random string from a list. I.e.:

 SELECT CHOOSE ('red', 'yellow', 'blue', 'green');

Is there an efficient way of replicating this in Postgres? Thanks!

Attempted to use CHOOSE() but it is not present in Postgres nor a known equivalent. Considering using RANDOM() to generate a value and making CASE statements to pick a string based on the value.

Tags:

2

Answers


  1. 0

    Create your own function:

    CREATE OR REPLACE FUNCTION choose(VARIADIC i_input TEXT[])
RETURNS text
    LANGUAGE SQL
AS
$$
    SELECT content
    FROM unnest(i_input) AS u(content)
    ORDER BY random()
    LIMIT 1;
$$;


SELECT CHOOSE ('red', 'yellow', 'blue', 'green');

    However, this function is limited to a maximum of 100 arguments. If you need more, you have to use an array as input:

    CREATE OR REPLACE FUNCTION choose(i_input TEXT[])
RETURNS text
    LANGUAGE SQL
AS
$$
    SELECT content
    FROM unnest(i_input) AS u(content)
    ORDER BY random()
    LIMIT 1;
$$;

SELECT CHOOSE(ARRAY['red', 'yellow', 'blue', 'green']);
    Login or Signup to reply.
  2. 0

    You can use array_sample with a parameter of 1, then take the first value.

    select (array_sample(array['red','yellow','blue','green'], 1))[1];

    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search