Postgresql – Improving the queries in a view to query custom types

postgresqlview

I'll spare you the details, but I've got a situation where I'm going to be creating and managing a lot of custom types. (Postgres 11.4, ultimate deployment under RDS.) It looks like there isn't a system view to make reviewing types simple. Starting from some very helpful threads here, I bashed together a flattened view that lists each type's name, owner, schema, and attributes. I'm sure there's a way to get the same output with a simpler set of queries…but I don't know what they are. So I'm posting the code here in hopes that someone can improve it. And, if that doesn't happen, the current version may prove helpful to someone else eventually. 

-- View: data.custom_types
DROP VIEW IF EXISTS data.custom_types;

CREATE OR REPLACE VIEW data.custom_types AS

with type_info as (
   select t.typrelid as type_oid,
          t.typowner::regrole::text as type_owner, -- ::text to make for easy searching
          n.nspname as type_schema,
          t.typname as type_name

     from pg_type t
left join pg_catalog.pg_namespace n on n.oid = t.typnamespace

    where (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')
 )

-- Get the position, name, and type details of a custom type.
select type_info.type_owner,
       type_info.type_schema,
       type_info.type_name,
       pg_attribute.attnum as attribute_number,
       pg_attribute.attname as attribute_name,
       pg_type.typname as attribute_type

  from pg_attribute
  join pg_type   on (pg_attribute.atttypid = pg_type.oid)
  join pg_class  on (pg_attribute.attrelid = pg_class.oid)
  join type_info on (pg_attribute.attrelid = type_info.type_oid);

Note that this version of the query is deliberately not picking up custom types created by an extension. That's what I need here, but it other situations call for getting all of the types.

Then you can query or group by any of the columns. Just to show an example:

select * from custom_types where type_name = 'state_v1' order by attribute_number;

type_owner    type_schema    type_name    attribute_number  attribute_name    attribute_type
user_change_structure    api    state_v1    1               name              citext
user_change_structure    api    state_v1    2               abbr              citext
user_change_structure    api    state_v1    3               population        int8
user_change_structure    api    state_v1    4               total_sq_miles    float4
user_change_structure    api    state_v1    5               percent_land      float4
user_change_structure    api    state_v1    6               statehood_year    int2

It's also handy for GROUP BY and ARRAY_AGG queries.

 select type_schema,
        type_name,
        type_owner,
        count(*) as attribute_count,
        array_agg(attribute_name order by attribute_number) as attribute_names

   from custom_types
  where type_name like 'state_v%'

group by type_schema,
          type_name,
         type_owner

 order by type_name;

type_schema type_name   type_owner  attribute_count attribute_names
api state_v1    user_change_structure   6   {name,abbr,population,total_sq_miles,percent_land,statehood_year}
api state_v2    user_change_structure   8   {id,name,abbr,population,total_sq_miles,percent_land,statehood_year,capital}

Best Answer

Four comments:

  • You should mention that you are only querying for composite types.

  • You don't need to join with pg_namespace, you can just cast the schema's oid to regnamespace.

  • You could turn the subselect into a join for potentially better performance and readability.

  • The NOT EXISTS clause shouldn't be in the subselect, but in the main query.

Related Question