iterate through Postgres enum values?



  • I have a Postgres enumerated type used to describe the node class (order sensitive) of a source/destination relationship table. Question is, how can I dynamically fetch the next value (if any) of an enumeration instance?

    CREATE TYPE MyEnum AS ENUM ('level1', 'level2', 'level3');
    

    SELECT dst_type, dst_id
    FROM my_relationships
    WHERE src_type::MyEnum = next(dst_type::MyEnum) -- does not work
    -- WHERE src_type::MyEnum = dst_type::MyEnum + 1 -- does not work
    AND src_id = 'd4fc1d5d-b054-d37c-4e80-2c04a6b3dbf8';



  • I don't think there is anything built-in, but you can write function to do that:

    create function next_enum(p_current myenum)
      returns myenum
    as
    $$
      select t.name
      from unnest(enum_range(NULL::myenum)) with ordinality as t(name,idx)
      where name > p_current
      order by idx
      limit 1;
    $$
    language sql
    stable;
    

    Then this should work:

    SELECT dst_type, dst_id
    FROM my_relationships
    WHERE src_type = next_enum(dst_type)
      AND src_id = 'd4fc1d5d-b054-d37c-4e80-2c04a6b3dbf8';
    

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=973783bf6182ad8a04ca0b5036b0f99f




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2