Re: Getting the Type Definition in the information schema

[Michael Glaesemann <grzm@xxxxxxxxxxxxx>]

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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq