Re: Getting the Type Definition in the information schema

On Jun 5, 2005, at 6:17 PM, Craig Bryden wrote:


> I am trying to figure out which view in the information schema or
> the system
> catalogs will give me the members/columns of a user defined data type.
> Any help with this would be great.
>

I believe you're referring to the composite variety of user-defined
data types. In some ways, a composite type is just a table that has
no rows.  Either way, you'll want to look in pg_type, pg_class, and
pg_attribute. The system catalog documentation is quite good.

http://www.postgresql.org/docs/8.0/interactive/catalogs.html

Here's a quick example (v8.0.3):

test=# create type foo as (
     foo_text text
     , foo_int integer
);

test=# select
     t.typname
     , t.typtype
     , attname
     , a.typname
     , a.typtype
from pg_type t
join pg_class on (reltype = t.oid)
join pg_attribute on (attrelid = pg_class.oid)
join pg_type a on (atttypid = a.oid)
where t.typname = 'foo';
typname | typtype | attname  | typname | typtype
---------+---------+----------+---------+---------
foo     | c       | foo_int  | int4    | b
foo     | c       | foo_text | text    | b
(2 rows)

If you're using psql, the easiest way is just to use the \d psql
command:

test=# \d foo
Composite type "public.foo"
   Column  |  Type
----------+---------
foo_text | text
foo_int  | integer

You can also see the SQL used by the \ commands by starting psql with
the -E flag:

temporal=# \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 pg_catalog.pg_table_is_visible(c.oid)
       AND c.relname ~ '^foo$'
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules,
relhasoids , reltablespace
FROM pg_catalog.pg_class WHERE oid = '42460'
**************************

********* QUERY **********
SELECT a.attname,
   pg_catalog.format_type(a.atttypid, a.atttypmod),
   (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
    WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND
a.atthasdef),
   a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '42460' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
**************************

Composite type "public.foo"
   Column  |  Type
----------+---------
foo_text | text
foo_int  | integer

Note there's a lot more detail here than my first query.

Hope this helps.

Michael Glaesemann
grzm myrealbox com