PostgreSQL: Return ENUM values like data_type modifiers

Question

When querying the datatype using format_type(tttypid, atttypmod) from pg_attribute, I would like to return the ENUM values like atttypmod values.

attname | type
--------+-------------------------------
a       | numeric(12,3)
b       | timestamp(0) without time zone
enum    | color('Red', 'Green', 'Blue')

Currently, the format_type() call only returns the enum name without values. I believe I need to CASE my select statement against the pg_type and pg_enum tables, but am having trouble with the proper syntax. My attempt is below. Thank you!

SELECT 
    attname,
    CASE
        WHEN format_type(atttypid, atttypmod)::text = ANY(select ARRAY(select t.typname::text from pg_type t join pg_enum e on t.oid = e.enumtypid group by t.typname))
            THEN format_type(atttypid, atttypmod) || '(' || array_to_string(enum_range(NULL::format_type(atttypid, atttypmod)), ',') || ')'
        ELSE format_type(atttypid, atttypmod)
        END AS type
FROM   pg_attribute
WHERE  attrelid = 'myTable'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

Answer 1

You need a subquery on pg_enum that aggregates the values. This gets easier in your main query if you join to pg_type, so that you can use a CASE expression that uses different paths depending on the column's type

SELECT a.attname, 
       case t.typtype
          when 'e' 
            then format_type(atttypid, atttypmod)||'('||
                  (select string_agg(quote_literal(e.enumlabel),',')
                   from pg_enum e
                   where t.oid = e.enumtypid)||')'
          else format_type(a.atttypid, a.atttypmod)
       end as column_type
FROM   pg_attribute a
  JOIN pg_type t ON a.atttypid = t.oid
WHERE  a.attrelid = 'mytable'::regclass
AND    a.attnum > 0
AND    NOT a.attisdropped
ORDER  BY a.attnum;