pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
programmatically retrieve details of a custom Postgres type
5+ messages / 3 participants
[nested] [flat]

* programmatically retrieve details of a custom Postgres type
@ 2022-11-11 04:02  Konstantin Izmailov <pgfizm@gmail.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Konstantin Izmailov @ 2022-11-11 04:02 UTC (permalink / raw)
  To: pgsql-general

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!

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: programmatically retrieve details of a custom Postgres type
@ 2022-11-11 04:09  Pavel Stehule <pavel.stehule@gmail.com>
  parent: Konstantin Izmailov <pgfizm@gmail.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Pavel Stehule @ 2022-11-11 04:09 UTC (permalink / raw)
  To: Konstantin Izmailov <pgfizm@gmail.com>; +Cc: pgsql-general

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  |  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


^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: programmatically retrieve details of a custom Postgres type
@ 2022-11-11 04:10  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Konstantin Izmailov <pgfizm@gmail.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Tom Lane @ 2022-11-11 04:10 UTC (permalink / raw)
  To: Konstantin Izmailov <pgfizm@gmail.com>; +Cc: pgsql-general

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: programmatically retrieve details of a custom Postgres type
@ 2022-11-11 04:30  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2022-11-11 04:30 UTC (permalink / raw)
  To: Konstantin Izmailov <pgfizm@gmail.com>; +Cc: pgsql-general

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: programmatically retrieve details of a custom Postgres type
@ 2022-11-11 15:33  Konstantin Izmailov <pgfizm@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Konstantin Izmailov @ 2022-11-11 15:33 UTC (permalink / raw)
  To: ; +Cc: pgsql-general

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
>

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2022-11-11 15:33 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-11 04:02 programmatically retrieve details of a custom Postgres type Konstantin Izmailov <pgfizm@gmail.com>
2022-11-11 04:09 ` Pavel Stehule <pavel.stehule@gmail.com>
2022-11-11 04:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-11 04:30   ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-11 15:33     ` Konstantin Izmailov <pgfizm@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox