Skip to content

Instantly share code, notes, and snippets.

@rgwood
Created January 30, 2022 23:56
Show Gist options
  • Save rgwood/d4125a7e551a2c08eb9ee11841e14afb to your computer and use it in GitHub Desktop.
Save rgwood/d4125a7e551a2c08eb9ee11841e14afb to your computer and use it in GitHub Desktop.
SELECT version();
SELECT ns.nspname, t.oid, t.typname, t.typtype, t.typnotnull, t.elemtypoid
FROM (
-- Arrays have typtype=b - this subquery identifies them by their typreceive and converts their typtype to a
-- We first do this for the type (innerest-most subquery), and then for its element type
-- This also returns the array element, range subtype and domain base type as elemtypoid
SELECT
typ.oid, typ.typnamespace, typ.typname, typ.typtype, typ.typrelid, typ.typnotnull, typ.relkind,
elemtyp.oid AS elemtypoid, elemtyp.typname AS elemtypname, elemcls.relkind AS elemrelkind,
CASE WHEN elemproc.proname='array_recv' THEN 'a' ELSE elemtyp.typtype END AS elemtyptype
FROM (
SELECT typ.oid, typnamespace, typname, typrelid, typnotnull, relkind, typelem AS elemoid,
CASE WHEN proc.proname='array_recv' THEN 'a' ELSE typ.typtype END AS typtype,
CASE
WHEN proc.proname='array_recv' THEN typ.typelem
WHEN typ.typtype='r' THEN rngsubtype
WHEN typ.typtype='d' THEN typ.typbasetype
END AS elemtypoid
FROM pg_type AS typ
LEFT JOIN pg_class AS cls ON (cls.oid = typ.typrelid)
LEFT JOIN pg_proc AS proc ON proc.oid = typ.typreceive
LEFT JOIN pg_range ON (pg_range.rngtypid = typ.oid)
) AS typ
LEFT JOIN pg_type AS elemtyp ON elemtyp.oid = elemtypoid
LEFT JOIN pg_class AS elemcls ON (elemcls.oid = elemtyp.typrelid)
LEFT JOIN pg_proc AS elemproc ON elemproc.oid = elemtyp.typreceive
) AS t
JOIN pg_namespace AS ns ON (ns.oid = typnamespace)
WHERE
typtype IN ('b', 'r', 'm', 'e', 'd') OR -- Base, range, multirange, enum, domain
(typtype = 'c' AND relkind='c') OR -- User-defined free-standing composites (not table composites) by default
(typtype = 'p' AND typname IN ('record', 'void')) OR -- Some special supported pseudo-types
(typtype = 'a' AND ( -- Array of...
elemtyptype IN ('b', 'r', 'm', 'e', 'd') OR -- Array of base, range, multirange, enum, domain
(elemtyptype = 'p' AND elemtypname IN ('record', 'void')) OR -- Arrays of special supported pseudo-types
(elemtyptype = 'c' AND elemrelkind='c') -- Array of user-defined free-standing composites (not table composites) by default
))
ORDER BY CASE
WHEN typtype IN ('b', 'e', 'p') THEN 0 -- First base types, enums, pseudo-types
WHEN typtype = 'r' THEN 1 -- Ranges after
WHEN typtype = 'm' THEN 2 -- Multiranges after
WHEN typtype = 'c' THEN 3 -- Composites after
WHEN typtype = 'd' AND elemtyptype <> 'a' THEN 4 -- Domains over non-arrays after
WHEN typtype = 'a' THEN 5 -- Arrays after
WHEN typtype = 'd' AND elemtyptype = 'a' THEN 6 -- Domains over arrays last
END;
-- Load field definitions for (free-standing) composite types
SELECT typ.oid, att.attname, att.atttypid
FROM pg_type AS typ
JOIN pg_namespace AS ns ON (ns.oid = typ.typnamespace)
JOIN pg_class AS cls ON (cls.oid = typ.typrelid)
JOIN pg_attribute AS att ON (att.attrelid = typ.typrelid)
WHERE
(typ.typtype = 'c' AND cls.relkind='c') AND
attnum > 0 AND -- Don't load system attributes
NOT attisdropped
ORDER BY typ.oid, att.attnum;
-- Load enum fields
SELECT pg_type.oid, enumlabel
FROM pg_enum
JOIN pg_type ON pg_type.oid=enumtypid
ORDER BY oid, enumsortorder;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment