Accessing values in anonymous row types in Postgres

If you are trying to build pivot tables in Postgres, you may find examples online that incorrectly push you towards using the “ROW” type to build the pivot table. You might try something like the following (which will not work):

create type event_counts as (event_type_1, event_type_2);

select 
  user,
  event_count[0].event_type1,
  event_count[0].event_type2
from (
  select 
    user, 
    array_agg(ROW(events)::event_counts order by event_type ASC) event_count
  from ( 
    select user, event_type, count(*) events
    from (
      select 'Event type 1' event_type, 'gsieling' as user
      union all
      select 'Event type 2' event_type, 'gsieling' as user
      union all 
      select 'Event type 1' event_type, 'otheruser' as user
    ) b
    group by 1, 2
  ) a

Unfortunately, the above example won’t actually work – you can’t cast the row to event_counts in this case (tested in 9.3). If you use an anonymous row type, it won’t work at all (there seems to be no way to get the contents). There are many confused stackoverflow posts on the subject, with the people providing “answers” blaming the asker.

The correct way to fix this is to switch to using an array and skip the row type entirely, which you can access by index:

select 
  user,
  event_count[1] event_1,
  event_count[2] event_2
from (
  select 
    user, 
    array_agg(events order by event_type ASC) event_count
  from ( 
    select user, event_type, count(*) events from (
      select 'Event type 1' event_type, 'gsieling' as user
      union all
      select 'Event type 2' event_type, 'gsieling' as user
      union all 
      select 'Event type 1' event_type, 'otheruser' as user
    ) b
    group by 1, 2
  ) a
) c