By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TYPE activity_type_enum
AS ENUM ('a',
'b',
'c');
CREATE TABLE some_table
(activities json);
INSERT INTO some_table
(activities)
VALUES ('{"a":"X1","b":"Y1","c":"Z1"}'),
('{"a":"X2","b":"Y2","c":"Z2"}'),
('{"a":"X3","b":"Y3","c":"Z3"}');
3 rows affected
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;
SELECT *
FROM some_view;
| a | b | c |
|---|---|---|
| X1 | Y1 | Z1 |
| X2 | Y2 | Z2 |
| X3 | Y3 | Z3 |