有没有一种方法来显示用户定义的 postgreql 枚举类型定义？

SELECT t.typname
FROM pg_class c JOIN pg_attribute a ON c.oid = a.attrelid JOIN pg_type t ON a.atttypid = t.oid
WHERE c.relname = 'your_type';

The tricky part was that simply selecting * from these views one does not get OIDs in the results.

It's \dT you're after, but it doesn't give it as a "CREATE" statement. You use \dD for domains.

\dT+ action.action_status
List of data types
Schema |         Name         | Internal name | Size | Elements | Description
--------+----------------------+---------------+------+----------+-------------
action | action.action_status | action_status | 4    | pending +|
|                      |               |      | live    +|
|                      |               |      | done    +|
|                      |               |      | notdone  |
(1 row)

If you just want the full name (type name and schema) and a sorted list of all enum labels, this query will do:

SELECT n.nspname AS "schema", t.typname
, string_agg(e.enumlabel, '|' ORDER BY e.enumsortorder) AS enum_labels
FROM   pg_catalog.pg_type t
JOIN   pg_catalog.pg_namespace n ON n.oid = t.typnamespace
JOIN   pg_catalog.pg_enum e ON t.oid = e.enumtypid
WHERE  t.typname = 'my_enum_type'
GROUP  BY 1,2;

Returns:

 schema | typname      | enum_labels
--------+--------------+-------------
public | my_enum_type | foo|bar

string_agg() requires Postgres 9.0 or later, replace with array_agg() for older versions.

To get the SQL CREATE statement, you could use pg_dump and look at the dump file.

Or, much more practically, use pgAdmin which displays reverse engineered SQL create scripts for any object in the database. Select it in the object browser and its create script is displayed in the SQL pane. There is even an option to copy the script to a newly opened window of the SQL editor automatically, where you can edit and execute it.

Check this:

select enum_range(null::my_type)

I think this is a much simpler solution :).

using this post, I've archived the goal to mimic the 'CREATE TYPE' in PgAdmin & PgBackup

WITH types AS (
SELECT n.nspname,
pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
CASE
WHEN t.typrelid != 0 THEN CAST ( 'tuple' AS pg_catalog.text )
WHEN t.typlen < 0 THEN CAST ( 'var' AS pg_catalog.text )
ELSE CAST ( t.typlen AS pg_catalog.text )
END AS obj_type,
coalesce ( pg_catalog.obj_description ( t.oid, 'pg_type' ), '' ) AS description
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n
ON n.oid = t.typnamespace
WHERE ( t.typrelid = 0
OR ( SELECT c.relkind = 'c'
FROM pg_catalog.pg_class c
WHERE c.oid = t.typrelid ) )
AND NOT EXISTS (
SELECT 1
FROM pg_catalog.pg_type el
WHERE el.oid = t.typelem
AND el.typarray = t.oid )
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
),
cols AS (
SELECT n.nspname::text AS schema_name,
pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
a.attname::text AS column_name,
pg_catalog.format_type ( a.atttypid, a.atttypmod ) AS data_type,
a.attnotnull AS is_required,
a.attnum AS ordinal_position,
pg_catalog.col_description ( a.attrelid, a.attnum ) AS description
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_type t
ON a.attrelid = t.typrelid
JOIN pg_catalog.pg_namespace n
ON ( n.oid = t.typnamespace )
JOIN types
ON ( types.nspname = n.nspname
AND types.obj_name = pg_catalog.format_type ( t.oid, NULL ) )
WHERE a.attnum > 0
AND NOT a.attisdropped
)
SELECT 'CREATE TYPE ' || cols.schema_name || '.' || cols.obj_name || E' AS (\n    ' ||
pg_catalog.array_to_string (ARRAY(
SELECT cols.column_name || ' ' || cols.data_type AS col_num_typ
FROM cols
WHERE cols.obj_name='my_user_data_type'
ORDER BY cols.schema_name,
cols.obj_name,
cols.ordinal_position ), E',\n    '
) || E'\n);'
AS cre_typ
FROM cols
WHERE cols.obj_name='my_user_data_type'
LIMIT 1

and run it under psql with this command to have only the SQL code :

\t\a\g\a\t