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.?
Add a comment
|
2 Answers
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.
1 Comment
Adam Kaplan
Although @francs's answer links off to the appropriate documentation, this answer is immediately usable, even 5 years later!
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
4 Comments
Antonio F.
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?
francs
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。(postgresql.org/docs/9.0/static/catalog-pg-attribute.html)
francs
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
Nathan Crause
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?