postgresql - PostgresQL - 如何在一个存储过程中插入主表和明细表？

Question

所以我正在做一个项目，它要求我的查询在一个事务中插入到一个主表及其详细表(将作为列表发送到数据库中)，这样它会在一个事务中回滚插入函数失败。

假设我有这些表:

CREATE TABLE transaction(
  id             BIGSERIAL PRIMARY KEY NOT NULL,
  user_id        BIGINT    FOREIGN KEY NOT NULL,
  total_item     INT                   NOT NULL DEFAULT 0,
  total_purchase BIGINT                NOT NULL DEFAULT 0
)

CREATE TABLE transaction_detail(
  id              BIGSERIAL PRIMARY KEY NOT NULL,
  transaction_id  BIGINT    FOREIGN KEY NOT NULL,
  product_id      BIGINT    FOREIGN KEY NOT NULL,
  product_price   INT                   NOT NULL DEFAULT 0,
  purchase_amount INT                   NOT NULL DEFAULT 0
)

我有这个功能:

创建或替换函数 create_transaction(order JSONB, product_list JSONB)

函数参数:

• order : 将被插入交易表的对象
• product_list:将被插入到 transaction_detail 表中的 Product 对象列表

我当前的查询看起来像这样:

CREATE OR REPLACE FUNCTION insert_order(tx JSONB, product_list JSONB)
    RETURNS BIGINT
AS $$
WITH result AS (
    INSERT INTO transaction(
        user_id,
        total_item,
        total_purchase,
    ) VALUES (
        (tx ->> 'user_id') :: BIGINT,
        (tx ->> 'total_item') :: INT,
        (tx ->> 'total_purchase') :: INT,
    )
    RETURNING id AS transaction_id 
)
FOR row IN product_list LOOP
    INSERT INTO transaction_detail(
        transaction_id,
        product_id,
        product_price,
        purchase_amount,
    ) VALUES (
        transaction_id,
        (row ->> 'product_id') :: BIGINT,
        (row ->> 'product_price') :: INT,
        (row ->> 'purchase_amount') :: INT,
    )
END LOOP;
$$ LANGUAGE SQL SECURITY DEFINER;

JSON 文件:

• tx.json

[
“用户 ID”:“1”，
“总项目”:“2”，
"total_purchase": "2000",
]

• product_list.json

[
{
“产品编号”:“1”，
“产品价格”:“500”，
“购买金额”:“2”
},
{
“产品编号”:“2”，
“产品价格”:“1000”，
“购买金额”:“1”
}
]

我知道我的查询有问题，但我无法确定。非常感谢任何指针。

Answer 1

假设作为product_list传递的数据是一个数组，你可以这样做:

CREATE OR REPLACE FUNCTION insert_order(p_order JSONB, p_product_list JSONB)
    RETURNS BIGINT
AS $$
  WITH result AS (
      INSERT INTO "transaction"(
          user_id,
          total_item,
          total_purchase
      ) VALUES (
          (p_order ->> 'user_id') :: BIGINT,
          (p_order ->> 'total_item') :: INT,
          (p_order ->> 'total_purchase') :: INT
      )
      RETURNING id AS transaction_id 
  ), details as (
    INSERT INTO transaction_detail(
        transaction_id,
        product_id,
        product_price,
        purchase_amount
    ) 
    select r.transaction_id, 
           (pl.data ->> 'product_id')::bigint,
           (pl.data ->> 'product_price')::int,
           (pl.data ->> 'purchase_amount')::int
    from result r,
      jsonb_array_elements(p_product_list) as pl(data)
  )
  select transaction_id 
  from result;
$$ 
LANGUAGE SQL SECURITY DEFINER;

我重命名了参数以避免名称与保留关键字冲突。通过为参数名称添加前缀，您还可以避免名称与列名或表名发生冲突。 order 是保留关键字，只能在引用时使用，例如“订单”。 transaction 是一个关键字，但它不是保留关键字，但最好还是引用它。

插入到交易详情中需要是一个INSERT...SELECT 从result 中选择以获取生成的交易id 并通过取消嵌套数组元素产品列表 JSON 值。

然后 CTE 的最终选择返回生成的交易 ID。

你可以这样调用函数:

select insert_order('{"user_id": 42, "total_item": 1, "total_purchase": 100}'::jsonb, 
                    '[ {"product_id": 1, "product_price": 10, "purchase_amount": 1}, 
                       {"product_id": 2, "product_price": 20, "purchase_amount": 2}, 
                       {"product_id": 3, "product_price": 30, "purchase_amount": 3} ]'::jsonb);