CODEWITHSUNDEEP
databasepostgresql
Larry
Larry

Reputation: 11899

How can I find the attribute information of User-defined types in Postgres?

Consider I have a user-defined type as follows:

CREATE TYPE mytype AS
 (myvar character(1),
  myvar2 bit(10));

Can I use any of Postgres’s information tables in order to get the attribute information for this type:

I.e. given “mytype”, the information I want is:

Upvotes: 1

Views: 1986

Answers (2)

Sergey Shevchuk
Sergey Shevchuk

Reputation: 161

In addition to response https://stackoverflow.com/a/9195771/6178141 I'd like to add some comments.

One needs to consider a couple of things here.

1) There can be deleted attributes. Yes, one can alter an existing type and add and/or remove attributes. Deleted attributes are flagged with TRUE in attisdropped column, but they do take attribute number. For instance, if we execute the following statements in additon to DTL above:

 ALTER TYPE mytype ADD ATTRIBUTE abc double precision;
 ALTER TYPE mytype DROP ATTRIBUTE myvar2;

then attribute abc will have physical number 2 while attnum value will be 3.

So we need to add this condition to WHERE clause:

AND NOT attisdropped

2) Also need to take into account type's schema. mytype will work for public schema. If type is created in a different schema, it won't work. In this case I personally prefer more the following condition:

WHERE attrelid = 'myschema.mytype'::regclass

Upvotes: 0

araqnid
araqnid

Reputation: 133402

Yes, the pg_type table will have the typrelid column populated. This is an OID key for the pg_class table, which then is used in pg_attribute tables, just as with tables, views, indices etc. So sth like:

select attname, format_type(atttypid, atttypmod)
from pg_type
     join pg_class on pg_class.oid = pg_type.typrelid
     join pg_attribute on pg_attribute.attrelid = pg_class.oid
where typname = 'mytype'
order by attnum

Upvotes: 1

Related Questions