Thread: programmatically retrieve details of a custom Postgres type
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...
Thank you!
pá 11. 11. 2022 v 5:02 odesílatel Konstantin Izmailov <pgfizm@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 | ColumnSizename | text | -1supplier_id | integer | 4price | numeric | 16Is 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 │ │ │ │
└────────┴───────────────────┴───────────┴──────────┴─────────┘
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)
┌─────────┬───────────────────┐
│ attname │ atttypid │
╞═════════╪═══════════════════╡
│ a │ integer │
│ b │ character varying │
│ c │ numeric │
└─────────┴───────────────────┘
(3 rows)
Thank you!
Regards
Pavel
Konstantin Izmailov <pgfizm@gmail.com> writes:
> I was unable to find how to get column names, sizes and types for a given
> composite type.
For a composite type, pg_type.typrelid links to pg_class and pg_attribute
entries that work much like a table. For instance,
# select a.attname, a.atttypid::regtype from pg_type t left join pg_attribute a on t.typrelid = a.attrelid where
typname= 'complex' order by attnum;
attname | atttypid
---------+------------------
r | double precision
i | double precision
(2 rows)
regards, tom lane
I wrote:
> For a composite type, pg_type.typrelid links to pg_class and pg_attribute
> entries that work much like a table.
Actually, you could reverse that: for a table, pg_type.typrelid links to
pg_class and pg_attribute entries that work much like a composite type.
For both relations and composite types, there are pg_class and pg_type
entries that (by convention only) have the same names and namespaces.
They cross-link to each other via pg_class.reltype and pg_type.typrelid.
The associated pg_attribute entries have attrelid matching the pg_class
OID. The catalog entries for the two cases are pretty nearly
indistinguishable except for pg_class.relkind. Again, it's only by
convention that we consider that the pg_type entry is primary for a
composite type but pg_class is primary for a relation.
Of course, a relation has some underlying storage (for most relkinds),
and it will likely have associated entries in other catalogs that a
composite type won't. But the core catalog entries are about the same.
regards, tom lane
Thank you, Pavel and Tom! It works great!
On Thu, Nov 10, 2022 at 9:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
I wrote:
> For a composite type, pg_type.typrelid links to pg_class and pg_attribute
> entries that work much like a table.
Actually, you could reverse that: for a table, pg_type.typrelid links to
pg_class and pg_attribute entries that work much like a composite type.
For both relations and composite types, there are pg_class and pg_type
entries that (by convention only) have the same names and namespaces.
They cross-link to each other via pg_class.reltype and pg_type.typrelid.
The associated pg_attribute entries have attrelid matching the pg_class
OID. The catalog entries for the two cases are pretty nearly
indistinguishable except for pg_class.relkind. Again, it's only by
convention that we consider that the pg_type entry is primary for a
composite type but pg_class is primary for a relation.
Of course, a relation has some underlying storage (for most relkinds),
and it will likely have associated entries in other catalogs that a
composite type won't. But the core catalog entries are about the same.
regards, tom lane