PostgreSQL插入选择多行 [英] Postgresql Insert select with multiple rows

问题描述

我正在创建一个过程来解析输入的json数据并存储在表中。该函数看起来像：

I am creating a procedure to parse input json data and stored in tables. The function looks like:

create or replace function test_func(d json)
returns void as $$
  begin
    with n as (
     insert into t1 (name) values (d::json -> 'name') returning id
    ), c as (
     insert into t2 (cars) values json_array_elements_text(d::json -> 'cars') returning id
    )
    insert into t3 (id, name_id, cars_id, brand)
    select 1, n.id, c.id, json_array_elements_text(d::json -> 'brands') from n, c;
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
)

数据输入名称为文本，汽车和品牌是数组，都包装在json中。
因此，最后一个插入具有混合值类型，如果此人有两辆汽车，我会在t3中插入4行，因为c.id和json_array_elements_text（d :: json->'brands'）都有两个数据集， 2x2 = 4，如何将插入的值一对一映射？因此，第一个c.id应该映射到第一个品牌。

the data input has name as text, cars and brands are array, all wrapped in a json. So the last insert has mixed value types, if the person has two cars, i got 4 rows inserted into t3 because c.id and json_array_elements_text(d::json -> 'brands') both have two data sets, 2x2 = 4, how can I map the inserted value to one on one? so 1st c.id should be mapped to 1st brand.

推荐答案

要映射它们，您必须不是在true上加入，而是在

To map them you have to join not on true, but on distinct row.

下面是示例如何使用常规将id 都加入-希望对您有所帮助。根据您的json样本

here is example how to join both on id with ordinality - hope it will help. based on your json sample

t=# with j as (select '{"name":"john", "cars":["bmw X5 xdrive","volvo v90 rdesign"], "brands":["bmw","volvo"]}'::json d)
select car,brand,t1.id 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
;
        car        | brand | id
-------------------+-------+----
 bmw X5 xdrive     | bmw   |  1
 volvo v90 rdesign | volvo |  2
(2 rows)

更新：详细介绍了OP：

update elaborating for OP:

您可以通过汇总em然后使用索引来避免映射多行：

you can avoid mapping multiple rows, by aggregating em and then using index:

您的fn ：

create or replace function test_func(d json)
returns void as $$
  begin
with j as (select d)
, a as (
  select car,brand,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
)
, n as (
  insert into t1 (name) values (d::json -> 'name') returning id
), c as (
  insert into t2 (cars) select car from a order by oid returning id
)
, 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 );
CREATE TABLE t3 ( "id" int, "name_id" int REFERENCES t1(id), "cars_id" int REFERENCES t2(id), "brand" text );

执行：

t=#   select test_func('{"name":"john", "cars":["bmw X5 xdrive","volvo v90 rdesign"], "brands":["bmw","volvo"]}');
 test_func
-----------

(1 row)

t=#   select * from t1;
 id |  name
----+--------
 14 | "john"
(1 row)

t=#   select * from t2;
 id |       cars
----+-------------------
 27 | bmw X5 xdrive
 28 | volvo v90 rdesign
(2 rows)

t=#   select * from t3;
 id | name_id | cars_id | brand
----+---------+---------+-------
  1 |      14 |      27 | bmw
  1 |      14 |      28 | volvo
(2 rows)

这篇关于PostgreSQL插入选择多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！