postgresql - 在 View 上使用 'instead of' 触发器为 jsonb 数组中的每个元素插入

Question

我正在使用 View 将相关行报告为数组。在 View 上使用 而不是 insert 触发器插入这些行会很方便。要插入的行将需要使用相关表中的键构造一个 json 对象。

我当前不完整的实现需要 where task in JSONB_ARRAY。我不知道这是否可能。

---

除了具有主 ID 列 vid 的 visit 表之外，我还有

task 从中提取的表。

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

和一个要填充的visit_task表

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

想要:

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

实际做:

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);

当前不完整的触发器解决方案摘录:

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

不幸的是，服务器不知道如何处理/在 MAGIcflags中。我也不会。

Answer 1

为了使任务更容易，创建一个辅助函数来将 sections 扩展为所需的格式:

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)

在触发函数中使用函数:

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)    

---

如果您不想创建辅助函数，请使用此变体:

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 $$;