SQL, Get a list of all the custom data types

Question

I need to retrieve a list of all the user defined data types in sql(oracle and postgres) using either an sql command or jdbc. For now I only know how to retrieve the source code with

SELECT text FROM all_source WHERE name='type'

Community · Accepted Answer · 2017-05-23 12:13:23Z

1

Oracle has a data dictionary view for almost everything. You can select from ALL_TYPES and ALL_TYPE_ATTRS. The latter shows the owner of each type.

Postgres does not have the same functionality so you can use this query from here

SELECT      n.nspname as schema, t.typname as type 
FROM        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 NOT IN ('pg_catalog', 'information_schema')

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Apr 5, 2015 at 17:35

kevinskio

4,5711 gold badge27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SteveL Over a year ago

SELECT * FROM ALL_TYPES where owner='myusername' ,this seems to work,do you know any equivalent view in postgres?

kevinskio Over a year ago

@SteveL Postgres does not have a view like Oracle. You could always make your own for your convenience.

Collectives™ on Stack Overflow

SQL, Get a list of all the custom data types

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related