PostgreSQL - check if value is equal to an enum value

Question

I have the following enum declaration:
CREATE TYPE known_roles_list AS ENUM ('role1', 'role2')

When the DB executes a certain query, I wish to check if they're doing it with one of these roles, and if not - let the query go through. Something like:

IF current_user NOT IN known_roles_list THEN
    RETURN OLD; 
END IF;

Obviously, that code above didn't work (raised runtime comparison errors). Nor did unnest()-ing the enum values and searching within them.

How can I make that search - and see if any of the enum's values match the current_user value? (The current_user value is just an example - later, I need to compare these enum values to a row value, denoted by column)

Thanks!

You could use row level security and add a policy on each table. — Laurenz Albe
– Laurenz Albe, Commented Aug 20, 2018 at 4:16

Łukasz Kamiński · Accepted Answer · 2018-08-20 08:36:35Z

10

You can use enum_range(null::known_roles_list) to get array listing elements of enum and then just use standard array operators. You need to cast that array to name[] or it won't be able to compare name and known_roles_list[].

postgres=> SELECT enum_range(null::known_roles_list);
  enum_range
---------------
 {role1,role2}

postgres=> SELECT current_user;
 current_user
--------------
 lkaminski

postgres=> SELECT current_user = any(enum_range(null::known_roles_list)::name[]);
 ?column?
----------
 f

postgres=> SELECT 'role2'::name = any(enum_range(null::known_roles_list)::name[]);
 ?column?
----------
 t

Enum functions: https://www.postgresql.org/docs/current/static/functions-enum.html

Any/Some: https://www.postgresql.org/docs/current/static/functions-comparisons.html#id-1.5.8.28.16

answered Aug 20, 2018 at 8:36

Łukasz Kamiński

6,0301 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adrian Tufa · Accepted Answer · 2018-08-20 07:27:56Z

4

Enumerated (enum) types are data types, so should be used when creating table structures

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';

answered Aug 20, 2018 at 7:27

Adrian Tufa

1065 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - check if value is equal to an enum value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related