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