1

I am using this query to list the values that are part of an enum:

SELECT unnest(enum_range(null::my_enum_name)) AS enum_values;

It works.

I am trying to make it more reusable by wrapping it in a function that takes the name of the enum (my_enum_name in the example above) as a parameter, but I can't find a lot of documentation on this.

Is it possible?

2
  • What have you tried so far? Have you tried creating a function? Did you encounter any errors? Why wouldn't it be possible? Welcome to DBA.SE.
    – John K. N.
    Commented Mar 31, 2020 at 11:22
  • Hi, thank you @Johnakahot2use. I've tried to type the parameter with regtype, but couldn't find a way to use it to cast null in the query.
    – tompave
    Commented Mar 31, 2020 at 12:51

1 Answer 1

4

It is possible:

create type mood as enum ('sad','ok', 'happy');
CREATE TYPE

create function f(p_typname text) returns setof text 
as $$
begin
   return query execute 
      format(
         'SELECT unnest(enum_range(null:: %s))::text AS enum_values',
         p_typname
      );
end;
$$
language plpgsql;
CREATE FUNCTION

select * from f('mood');
   f   
-------
 sad
 ok
 happy
(3 rows)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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