Creating columns from a enum type in PostgreSQL

Question

I have an enumerated type on PostgreSQL, and I want to create a view that has a column for each enumerated value.

My use case is similar to this question. I have a jsonb column that I want to turn into a view with columns made up of the keys of the json blob. The difference in my case is that the valid keys are defined in an enum rather than aggregated from the objects themselves.

The following SQL statement is essentially what I want to do, but doesn't work:

SELECT json_populate_record(null::activity_type_enum, activities) from some_table;

Is there a way to cast the enumerated type into what is expected by the first argument of json_populate_record?

Answer 1

That needs some dynamic SQL.

Assuming all the columns in the view should have text type.

1. We get the enum members as an array with enum_range() and transform them into a set using unnest().
2. To each enum member in the set we append ' text' and use string_agg() to build a comma separated list of them. Like that, we get a column definition.
3. We build a CREATE VIEW statement selecting from the table lateral cross joining json_to_record() aliased with the column definition we built.
4. Execute the CREATE VIEW statement with EXECUTE.

Together we get the following DO block:

DO
$$
BEGIN
  EXECUTE '
CREATE VIEW some_view
AS
  SELECT x.*
         FROM some_table t
              CROSS JOIN LATERAL json_to_record(t.activities) x(' || (SELECT string_agg(un.m || ' text', ', ')
                                                                             FROM unnest(enum_range(NULL::activity_type_enum)) un(m)) || ');
';
END;
$$
LANGUAGE plpgsql;

db<>fiddle

Replace json_to_record() with jsonb_to_record(), if the type of activities is jsonb rather than json.

If the enum changes however, the DO block has to be rerun, to have the view reflect the changes.