Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've created a few new UDTs in PostgreSQL. However, now I have two problems:

  1. how to see which UDTs have been defined?
  2. how to see the columns defined within these UDTs?

Unfortunately, I couldn't find anything on that in the PostgreSQL documentation.

share|improve this question

2 Answers 2

up vote 6 down vote accepted

Does this get you started?

SELECT n.nspname AS schema,
        pg_catalog.format_type ( t.oid, NULL ) AS name,
        t.typname AS internal_name,
        CASE
            WHEN t.typrelid != 0
            THEN CAST ( 'tuple' AS pg_catalog.text )
            WHEN t.typlen < 0
            THEN CAST ( 'var' AS pg_catalog.text )
            ELSE CAST ( t.typlen AS pg_catalog.text )
        END AS size,
        pg_catalog.array_to_string (
            ARRAY( SELECT e.enumlabel
                    FROM pg_catalog.pg_enum e
                    WHERE e.enumtypid = t.oid
                    ORDER BY e.oid ), E'\n'
            ) AS elements,
        pg_catalog.obj_description ( t.oid, 'pg_type' ) AS description
    FROM pg_catalog.pg_type t
    LEFT JOIN pg_catalog.pg_namespace n
        ON n.oid = t.typnamespace
    WHERE ( t.typrelid = 0
            OR ( SELECT c.relkind = 'c'
                    FROM pg_catalog.pg_class c
                    WHERE c.oid = t.typrelid
                )
        )
        AND NOT EXISTS
            ( SELECT 1
                FROM pg_catalog.pg_type el
                WHERE el.oid = t.typelem
                    AND el.typarray = t.oid
            )
        AND n.nspname <> 'pg_catalog'
        AND n.nspname <> 'information_schema'
        AND pg_catalog.pg_type_is_visible ( t.oid )
    ORDER BY 1, 2;

In psql you can \set ECHO_HIDDEN on to make psql show you the queries used to generate the output of the \d... commands. I've found these queries to be very useful as a starting point when digging metadata out of databases.

share|improve this answer
    
This tip with ECHO_HIDDEN is priceless. –  dezso Feb 26 '13 at 21:25
    
@dezso: You get the same with psql -E. –  Erwin Brandstetter Feb 27 '13 at 0:12
    
Thanks a lot for the code and the explanations. Just one thing: what do you mean by ´\d...´ commands? –  navige Feb 27 '13 at 8:35
1  
Type \? in psql or read the manual here to get a list of meta-commands plus explanation. \d (& friends) describe objects of the database. Internally, most meta-commands are executed as SQL against the system catalog, which can also displayed with the mentioned options. –  Erwin Brandstetter Feb 27 '13 at 14:25
    
Awesome stuff. I used this to create some code used in my Postgres driver. Saved me an hour of work. Yay! –  Jochen Bedersdorfer Dec 13 '13 at 5:13

You can also use the standard GUI pgAdmin:

types in pgAdmin

Make sure types are enabled in the object browser (Options - Browser - Display)

To the left you see user-defined types in the chosen schema (question 1.).

The SQL pane to the right has the reverse engineered SQL script for the selected type (question 2.).
More details in the pane above that, like Dependents etc.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.