insert for each element in jsonb array with 'instead of' trigger on a view

Question

I'm using a view to report related rows as an array. It would be convenient to also insert those rows with a instead of insert trigger on the view. The rows to insert will need to construct a json object with keys from a related table.

My current incomplete implementation needs where task in JSONB_ARRAY. I don't know if that is possible.

in addition to a visit table with primary id colum vid, I have

task table to pull from.

|  task  |       sections          |
+--------+-------------------------+
|task_A  | ["section1","section2"] |
|task_B  | ["part1",   "part2"   ] |

and a visit_task table to populate

| vid |  task  |                   measures                     |
+-----+--------+------------------------------------------------+
| 1   | task_C | ["level1": "value added later","level2": null] |
| 1   | task_E | ["q1": null,"q2": null]                        |

want:

insert into vt_view (vid,tasks) values (1,'["task_A","task_B"]::jsonb)

to actually do:

insert into visit_task (vid,task,measures) 
values (1,'task_A','{"section1": null, "section2": null}'::jsonb);
insert into visit_task (vid,task,measures) 
values (1,'task_B','{"part1": null, "part2": null}'::jsonb);

current incomplete trigger solution excerpt:

insert into visit_task from
 select  
  NEW.vid as vid,
  NEW.task as task,
  -- *MAGIC* to create json object; measures list becomes object with null vals 
  row_to_json(_) from (select json_object(t.measures, 'null')) as measures
  -- /MAGIC
  from task t
  -- *MAGIC* where we only grab the tasks within the array NEW.tasks
  where t.task in (select * from NEW.tasks) 
  -- /MAGIC

Unfortunately, the server doesn't know what to do with/in the MAGIC flags. Neither do I.

klin · Accepted Answer · 2016-01-23 01:45:46Z

1

To make the task easier create an auxiliary function to expand sections to desired format:

create or replace function expand_sections(jsonb)
returns jsonb language sql as $$
    select jsonb_object_agg(e, null)
    from jsonb_array_elements_text($1) e
$$;

-- test the function:
select task, expand_sections(sections)
from task;

  task  |           expand_sections            
--------+--------------------------------------
 task_A | {"section1": null, "section2": null}
 task_B | {"part1": null, "part2": null}
(2 rows)

Use the function in the trigger function:

create or replace function trigger_instead_on_vt_view()
returns trigger language plpgsql as $$
begin
    insert into visit_task
    select vid, task, expand_sections(sections)
    from (
        select new.vid, task
        from jsonb_array_elements_text(new.tasks) task
        ) sub
    join task using(task);
    return null;
end $$;

create trigger trigger_instead_on_vt_view
instead of insert on vt_view
for each row execute procedure trigger_instead_on_vt_view();

insert into vt_view (vid,tasks) values (1,'["task_A","task_B"]'::jsonb);

select * from visit_task;

 vid |  task  |               measures               
-----+--------+--------------------------------------
   1 | task_A | {"section1": null, "section2": null}
   1 | task_B | {"part1": null, "part2": null}
(2 rows)

If you do not want to create the auxiliary function, use this variant:

create or replace function trigger_instead_on_vt_view()
returns trigger language plpgsql as $$
begin
    insert into visit_task
    select vid, task, measures
    from (
        select new.vid, task
        from jsonb_array_elements_text(new.tasks) task
        ) s
    join (
        select task, jsonb_object_agg(e, null) measures
        from task, jsonb_array_elements_text(sections) e
        group by 1
        ) t
    using (task);
    return null;
end $$;

edited Jan 23, 2016 at 1:45

answered Jan 22, 2016 at 23:45

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Will Over a year ago

This works great! Thanks! I apparently do not have the patch(?) for jsonb_array_elements and jsonb_object_agg but converting to json works: ` create or replace function array_to_nullvalmap(jsonb) returns jsonb language sql as $$ select json_object_agg(e, null)::jsonb from json_array_elements_text($1::json) e $$; `

klin Over a year ago

jsonb_array_elements(jsonb) is in Postgres 9.4 and jsonb_agg(expression) in Postgres 9.5. I'm glad you have found equivalents.

Collectives™ on Stack Overflow

insert for each element in jsonb array with 'instead of' trigger on a view

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related