使用索引将多个数组的值插入表中 [英] Insert multiple arrays' values into tables using index
本文介绍了使用索引将多个数组的值插入表中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
续与此 post 一起,将创建一个函数来解析json输入,然后将值插入到三个表,其中先前插入的id作为最后一个的参数 插入.
Cont. with this post, a function is created to parse json input then insert values into three tables, with previous inserted ids as parameter for last insert.
如果我想在同一张表中插入两个数组,我可以做
If i want to insert two arrays into the same table, i can just do
insert into t2 (car, car_type)
select json_array_elements_text(d::json -> 'car'),json_array_elements_text(d::json -> 'car_type')::int4 returning id;
)
如何使其与以下索引一起使用?
how to make it work with index as below?
功能
create or replace function test_func(d json)
returns void as $$
begin
with j as (select d)
, a as (
select car,brand,type, t1.id oid
from j
join json_array_elements_text(j.d->'cars') with ordinality t1(car,id) on true
join json_array_elements_text(j.d->'brands') with ordinality t2(brand,id)
on t1.id = t2.id
join json_array_elements_text(j.d->'car_type') with ordinality t2(type,id)
on t1.id = t2.id // this line apparently doesnt work, t2 has been joined twice
)
, n as (
insert into t1 (name) values (d::json -> 'name') returning id
), c as (
insert into t2 (cars,car_type) select car,type from a order by oid returning id // needs to insert two columns here from two arrays
)
, ag as (
select array_agg(c.id) cid from c
)
insert into t3 (id, name_id, cars_id, brand)
select 1, n.id,cid[oid], brand
from a
join n on true
join ag on true
;
end;
$$ language plpgsql;
表格:
CREATE TABLE t1 ( "id" SERIAL PRIMARY KEY, "name" text NOT NULL );
CREATE TABLE t2 ( "id" SERIAL PRIMARY KEY, "cars" text NOT NULL, "car_type" int );
CREATE TABLE t3 ( "id" int, "name_id" int REFERENCES t1(id), "cars_id" int REFERENCES t2(id), "brand" text );
测试:
select test_func('{"name":"john", "cars":["bmw X5 xdrive","volvo v90 rdesign"], "brands":["bmw","volvo"],"car_type":[1,1]}');
推荐答案
您使用t2为两个不同的集合混叠-尝试:
you used t2 for aliasing two different sets - try:
create or replace function test_func(d json)
returns void as $$
begin
with j as (select d)
, a as (
select car,brand,car_type, t1.id oid
from j
join json_array_elements_text(j.d->'cars') with ordinality t1(car,id) on true
join json_array_elements_text(j.d->'brands') with ordinality t2(brand,id)
on t1.id = t2.id
join json_array_elements_text(j.d->'car_type') with ordinality car_t(car_type,id)
on t1.id = car_t.id
)
, n as (
insert into t1 (name) values (d::json -> 'name') returning id
), c as (
insert into t2 (cars,car_type) select car,car_type::int from a order by oid returning id -- needs to insert two columns here from two arrays
)
, ag as (
select array_agg(c.id) cid from c
)
insert into t3 (id, name_id, cars_id, brand)
select 1, n.id,cid[oid], brand
from a
join n on true
join ag on true
;
end;
$$ language plpgsql;
结果:
t=# select * from t2;
id | cars | car_type
----+-------------------+----------
1 | bmw X5 xdrive | 1
2 | volvo v90 rdesign | 1
(2 rows)
这篇关于使用索引将多个数组的值插入表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文