PostgreSQL - Function that returns labels of any ENUM

Question

I am trying to write a "generic" function that will return labels of any enum (in any schema)... But I am not having much luck because I am not sure what should argument type be...

The goal would be to be able to call function like this

SELECT common.get_enum_labels('public.rainbow_colors');
SELECT common.get_enum_labels('audit.user_actions');

This is what I have so

CREATE OR REPLACE FUNCTION common.get_enum_labels(enum_name regtype)
RETURNS SETOF text AS
$$
BEGIN
  EXECUTE format('SELECT unnest(enum_range(NULL::%s))::text AS enum_labels ORDER BY 1', enum_name);
END;
$$
LANGUAGE plpgsql
STABLE
PARALLEL SAFE
;

Any tips would be appreciated

klin · Accepted Answer · 2017-07-08T00:40:29.030

2

The argument type should be regtype, do not forget to return something from the function

CREATE OR REPLACE FUNCTION get_enum_labels(enum_name regtype)
RETURNS SETOF text AS
$$
BEGIN
    RETURN QUERY
        EXECUTE format('SELECT unnest(enum_range(NULL::%s))::text ORDER BY 1', enum_name);
END;
$$
LANGUAGE plpgsql

edited Jul 08 '17 at 00:40

answered Jul 08 '17 at 00:28

klin

112,967
15
204
232

Will text be prone to SQL injection? Is there an appropriate object identifier type that I should use? I am asking because of [this answer](https://stackoverflow.com/a/10711349/481904) – zam6ak Jul 08 '17 at 00:36

score 1 · Answer 2 · answered Jul 08 '17 at 00:46

1

create or replace function get_enum_labels(enum_name regtype)
  returns setof text language sql stable 
as $$
  select enumlabel::text
  from pg_enum
  where enumtypid = enum_name
  order by enumsortorder
$$;

answered Jul 08 '17 at 00:46

Abelisto

14,826
2
33
41

PostgreSQL - Function that returns labels of any ENUM

2 Answers2