PLPGSQL: Test if an element is found in an array literal

Question

Here is my code:

CREATE OR REPLACE FUNCTION public.colors(color text) RETURNS void LANGUAGE plpgsql AS $function$ DECLARE colorOptions text []; BEGIN colorOptions := '{ red, orange, yellow }'; IF color <> ANY (colorOptions) THEN raise notice 'This color is not accepted!'; END IF; END; $function$;

The problem is that it raises a notice for any value I send it, irrespective of whether that value is in the array of colors or not.

For example, all of these raise a notice:

SELECT colors ('red'); SELECT colors ('black'); SELECT colors ('rubbish');

I tried using IN instead of ANY, as follows:

IF color NOT IN (colorOptions) THEN

That produces the following error:

Query 1 ERROR: ERROR: operator does not exist: text <> text[] LINE 1: color NOT IN (colorOptions)

Use <> ALL (...)

user330315
– user330315

2022-10-11 05:47:49 +00:00
Commented Oct 11, 2022 at 5:47 — user330315
– user330315, Commented Oct 11, 2022 at 5:47

jian · Accepted Answer · 2022-10-11 04:34:54Z

expression operator ANY (array expression)
expression operator SOME (array expression)
The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of ANY is “true” if any true result is obtained. The result is “false” if no true result is found (including the case where the array has zero elements).

Correct me if I am wrong. I think color <> ANY (colorOptions) will always be true unless color = colorOptions.

Try not (color = ANY (colorOptions))

Collectives™ on Stack Overflow

PLPGSQL: Test if an element is found in an array literal

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related