顯示使用者定義的類型及其詳細資訊

我在 PostgreSQL 中創建了一些新的 UDT。但是，現在我有兩個問題：

1. 如何查看已定義的 UDT？
2. 如何查看這些 UDT 中定義的列？

不幸的是，我在 PostgreSQL 文件中找不到任何相關內容。

這會讓你開始嗎？

SELECT n.nspname AS schema,
       pg_catalog.format_type ( t.oid, NULL ) AS name,
       t.typname AS internal_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 size,
       pg_catalog.array_to_string (
           ARRAY( SELECT e.enumlabel
                   FROM pg_catalog.pg_enum e
                   WHERE e.enumtypid = t.oid
                   ORDER BY e.oid ), E'\n'
           ) AS elements,
       pg_catalog.obj_description ( t.oid, 'pg_type' ) AS description
   FROM pg_catalog.pg_type t
   LEFT 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 pg_catalog.pg_type_is_visible ( t.oid )
   ORDER BY 1, 2;

在 psql 中，您可以\set ECHO_HIDDEN on讓 psql 向您顯示用於生成\d...命令輸出的查詢。我發現這些查詢作為從數據庫中探勘元數據的起點非常有用。

更新：2019-12-16

對於復合類型，列元數據可以使用如下方式確定：

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 cols.schema_name,
       cols.obj_name,
       cols.column_name,
       cols.data_type,
       cols.ordinal_position,
       cols.is_required,
       coalesce ( cols.description, '' ) AS description
   FROM cols
   ORDER BY cols.schema_name,
           cols.obj_name,
           cols.ordinal_position ;

引用自：https://dba.stackexchange.com/questions/35497