Search code examples
sqlpostgresqloracle-sqldeveloperjdeveloper

What select query will list domains within Postgres?

I'm aware of the psql command that will list these, but I'm attempting to write an extension for Oracle's SQL Developer that will list them on the left-hand navigator panel. The XML format requires a select statement.

For example purposes, I'll include the code I cooked up for sequences:

<sql constrained="true">
    <![CDATA[SELECT relname FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace WHERE relkind = 'S' AND nspname = :SCHEMA]]>
</sql>

I've attempted to figure it out for myself, but I don't see anything with my test domain names in pg_class or any other of those internal tables (though it must be in one, somewhere).

Does anyone have a hint?

Solution

  • Asked too soon... here's a select query that will return domains from a specific schema:

    SELECT typname FROM pg_catalog.pg_type JOIN pg_catalog.pg_namespace ON pg_namespace.oid = pg_type.typnamespace WHERE typtype = 'd' AND nspname = 'someschema'

    Minor tweaking can also return enums, ranges, and composites/rows. More information in the docs.