Re: programmatically retrieve details of a custom Postgres type

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

pá 11. 11. 2022 v 5:02 odesílatel Konstantin Izmailov <pgfizm@xxxxxxxxx> napsal:

Hello,

I was unable to find how to get column names, sizes and types for a given composite type.

Example. For a type defines as:

  CREATE TYPE inventory_item AS (

  name text,

  supplier_id integer,

  price numeric

);

I have a plpgsql stored proc that returns SETOF inventory_item (i.e. there is no table with a column of this type).

I looked into the pg_type table but it only contains oid and typrelid for the inventory_item type. I need a query that returns information about structure of the composite type, i.e.:

  ColumnName  |  ColumnType  |  ColumnSize

  name             | text                | -1

  supplier_id      | integer           | 4

  price              | numeric          | 16

Is this possible? I'm executing queries via libpq...

 (2022-11-11 05:06:26) postgres=# create type foo as (a int, b varchar, c numeric);
CREATE TYPE
(2022-11-11 05:06:42) postgres=#
\q
[pavel@localhost isolation]$ psql -E
Assertions: on
psql (16devel)
Type "help" for help.

(2022-11-11 05:06:47) postgres=# \d foo
********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname OPERATOR(pg_catalog.~) '^(foo)$' COLLATE pg_catalog.default
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS relhasoids, c.relispartition, '', c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident, am.amname
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)
WHERE c.oid = '16389';
**************************

********* QUERY **********
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid, true)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
  a.attidentity,
  a.attgenerated
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '16389' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************

                  Composite type "public.foo"
┌────────┬───────────────────┬───────────┬──────────┬─────────┐
│ Column │       Type        │ Collation │ Nullable │ Default │
╞════════╪═══════════════════╪═══════════╪══════════╪═════════╡
│ a      │ integer           │           │          │         │
│ b      │ character varying │           │          │         │
│ c      │ numeric           │           │          │         │
└────────┴───────────────────┴───────────┴──────────┴─────────┘

Important part is:

(2022-11-11 05:08:20) postgres=# select attname, atttypid::regtype from pg_attribute where attrelid = 'foo'::regclass;
┌─────────┬───────────────────┐
│ attname │     atttypid      │
╞═════════╪═══════════════════╡
│ a       │ integer           │
│ b       │ character varying │
│ c       │ numeric           │
└─────────┴───────────────────┘
(3 rows)

Thank you!

Regards

Pavel