pá 11. 11. 2022 v 5:02 odesílatel Konstantin Izmailov <pgf...@gmail.com> 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