Determine if user-defined type is ENUM

Question

Is there a way to determine whether or not a user-defined type in PostgreSQL is an ENUM?

Essentially we have the following:

CREATE TYPE foo AS ENUM (
  'Sometimes',
  'You',
  'Wanna',
  'Go',
  'Where Everybody Knows Your Name'
);

With a table instantiated by:

CREATE TABLE bar (
  lyrics foo DEFAULT 'Wanna'::foo
);

I'm able to determine the type of foo from the column lyrics, however, I'm having trouble finding a way to determine whether or not foo is an ENUM.

For context, I need this information to programmatically get a list of all possible values of foo when given a column of lyrics.

Answer 1

You can do something like this,

SELECT true
FROM pg_enum
WHERE enumtypid = pg_typeof('You'::foo)::regtype
FETCH FIRST ROW ONLY;

You can create a simple function that does that too,

CREATE FUNCTION is_enum(x regtype)
RETURNS bool
AS $$
  SELECT true
  FROM pg_enum
  WHERE enumtypid = x
  FETCH FIRST ROW ONLY;
$$ LANGUAGE sql
IMMUTABLE;

SELECT is_enum(pg_typeof('You'::foo));
 is_enum 
---------
 t
(1 row)

Answer 2

As often with such questions, the \set ECHO_HIDDEN on command of psql helps. \dT+ will show the possible values of the enum, if the type in question is an enum. The query behind the output is rather complex, but one can simplify it to fit your needs like

SELECT format_type(t.oid, NULL) AS name,
       array_agg(e.enumlabel ORDER BY e.enumsortorder) AS elements
  FROM pg_type AS t 
  LEFT JOIN pg_enum AS e ON e.enumtypid = t.oid
 WHERE t.typname = 'foo'
 GROUP BY t.oid;

This will return {NULL} (an array with a NULL in it) if the type is not an enum, and the actual elements as an array otherwise. You can tweak it further if necessary.