时间戳

首页 > 解决方案 > Postgresql：使用存储过程的 JSON 参数插入具有有效 FK 的两个表

postgresql - Postgresql：使用存储过程的 JSON 参数插入具有有效 FK 的两个表

问题描述

我有以下两个表：

CREATE TABLE tableone (
  id            integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
  sampletextone text
);

CREATE TABLE tabletwo (
  id            integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
  tableone_id   int,
  sampletexttwo text
);

将以下JSON参数发送到过程：

[
  {
    "sampletextone": "table one text 1",
    "tabletwo": 
      [
        {
            "sampletexttwo": "table two sample one text 1"
        },
        {
            "sampletexttwo": "table two sample one text 2"
        }
      ]
  },
  {
    "sampletextone": "table one text 2",
    "tabletwo": 
      [
        {
            "sampletexttwo": "table two sample one text 3"
        },
        {
            "sampletexttwo": "table two sample one text 4"
        }
      ]
  }
]

我有以下stored procedure

CREATE OR REPLACE procedure testproc(jsonparam json)
  AS 
$BODY$

  WITH ins1 AS (INSERT INTO "tableone" ("sampletextone") 
    SELECT prop->>'sampletextone'
    FROM json_array_elements(jsonparam) prop 
    Returning "id"
 )
 INSERT INTO "tabletwo" ("tableone_id", "sampletexttwo") 
 SELECT ins1."id", 'should be all sample text two'
 FROM ins1
 --JOIN json_array_elements(jsonparam) prop;

$BODY$

LANGUAGE sql;

我正在尝试再次加入参数以使用正确的外键JSON插入所有四行。我不确定再次加入相关数据的最佳方式是什么。tabletwotableone

在线示例

更新：

CREATE OR REPLACE procedure testproc(jsonparam json)
 AS 
$BODY$

 WITH ins1 AS (INSERT INTO "tableone" ("sampletextone") 
 SELECT prop->>'sampletextone'
 FROM json_array_elements(jsonparam) prop 
 Returning "id", "sampletextone"
)
INSERT INTO "tabletwo" ("tableone_id", "sampletexttwo") 
SELECT ins1."id", json_extract_path(prop, 'tabletwo', 'sampletexttwo')
FROM ins1
JOIN json_array_elements(jsonparam) prop ON prop->>'sampletextone' = 
ins1."sampletextone"

$BODY$

LANGUAGE sql;

使用正确的外键而不是 4创建更新的stored procedure仅 2 行并且为空tabletwosampletexttwo

标签： postgresqljsonb

解决方案

我找到了答案

CREATE OR REPLACE procedure testproc(jsonparam json)
 AS 
$BODY$

WITH ins1 AS (INSERT INTO "tableone" ("sampletextone") 
 SELECT prop->>'sampletextone'
 FROM json_array_elements(jsonparam) prop 
 Returning "id", "sampletextone"
)
INSERT INTO "tabletwo" ("tableone_id", "sampletexttwo") 
SELECT ins1."id", json_array_elements(json_extract_path(prop, 'tabletwo'))->>'sampletexttwo'
FROM ins1
JOIN json_array_elements(jsonparam) prop ON prop->>'sampletextone' = 
ins1."sampletextone"

$BODY$

LANGUAGE sql;

如果有更好的方法，请告诉我

推荐阅读