6

Where is information about user-defined types stored?

Are there some tables which contain information about the fields of a user-defined composite type, their names, etc.?

Alex Riley
  • 169,130
  • 45
  • 262
  • 238
Antonio F.
  • 411
  • 2
  • 9
  • 16

2 Answers2

5

Information about the fields constituting a composite type can be retrieved like this:

select * from pg_attribute where attrelid =
  (select typrelid from pg_type where typname = 't_employee')

where t_employee would be the name of the composite type.

oberstet
  • 21,353
  • 10
  • 64
  • 97
  • 2
    Although @francs's answer links off to the appropriate documentation, this answer is immediately usable, even 5 years later! – Adam Kaplan Dec 30 '17 at 22:11
4

The catalog pg_type stores information about data types. Base types and enum types (scalar types) are created with CREATE TYPE, and domains with CREATE DOMAIN.

More information about pg_type plz visit http://www.postgresql.org/docs/9.0/static/catalog-pg-type.html

francs
  • 8,511
  • 7
  • 39
  • 43
  • Thanks for your reply. pg_type does not store information about fields that costitute composite types. Do you know, if it exists, which table store this kind of information? – Antonio F. Aug 08 '11 at 09:23
  • I'm not sure if I understand your question very well, if you want to know the information about all the columns of a table , you can query the catalog pg_attribute。(http://www.postgresql.org/docs/9.0/static/catalog-pg-attribute.html) – francs Aug 09 '11 at 01:24
  • By the way, pg_type.typtype is b for a base type, c for a composite type (e.g., a table's row type), d for a domain, e for an enum type, or p for a pseudo-type. See also typrelid and typbasetype – francs Aug 09 '11 at 01:24
  • Here's an example of what I think is being asked here (and how I ended up here, too). We've got a composite type named "listing_room" containing data (for example) "name text, floor int, width decimal, depth decimal" (yes, I know this isn't valid SQL, but I'm just making a point). I can now trundle off and create a table "listings" with columns "id serial, address text, master_bedroom room". So, using any database interface (in my case, JDBC), I can determine that "master_bedroom" is of type "room", but how can I query the server to dynamically determine "room"'s actual structure? – Nathan Crause Apr 06 '17 at 19:24