Postgresql复杂逻辑函数（plpgsql）
好的，我有这个模式
/?rdbms=postgres_10&fiddle=56b0781c13dfb545e9f07f82da6ae34d
- 每张帐单将一堆产品保存在bill_products表中
- 此Bill_products将在不久的将来随机交付和更新
我需要制定一个可以像这样输入jsonb的函数
[
{
bill_id : 1,
product_id : 1,
delivered: 50
},
{
bill_id : 1,
product_id : 2,
delivered: 400
}
]
采用上述jsonb输入，该函数应更新bill_products并将其插入inventory表中。同样，如果所有bill_products均已交付，则它应将bill表
中的批准布尔值更新为true。我是plpgsql的新手，有人可以帮助我吗？
回答如下：DO LANGUAGE plpgsql $$
DECLARE
input CONSTANT jsonb := '...';
delivery jsonb;
updated bill_products;
BEGIN
FOR delivery IN SELECT jsonb_array_elements(input) LOOP
UPDATE bill_products
SET delivered = delivered + (delivery->>'delivered')::numeric
WHERE bill_id = (delivery->>'bill_id')::int
AND pro_id = (delivery->>'product_id')::int
RETURNING *
INTO STRICT updated;
INSERT INTO inventory(pro_id, quantity)
VALUES (updated.pro_id, updated.delivered);
END LOOP;
UPDATE bill
SET approved = NOT EXISTS (SELECT 1
FROM bill_products
WHERE bill_products.bill_id = bill.bill_id
AND delivered < quantity)
WHERE bill_id IN (SELECT DISTINCT (d->>'bill_id')::int
FROM jsonb_array_elements(input) d);
END;
$$;
（online demo）
或者，使用普通SQL而不是循环可能更简单：
WITH updates AS ( UPDATE bill_products SET delivered = delivered + (delivery->>'delivered')::numeric FROM jsonb_array_elements(input) delivery WHERE bill_id = (delivery->>'bill_id')::int AND pro_id = (delivery->>'product_id')::int RETURNING pro_id, delivered ) INSERT INTO inventory(pro_id, quantity) SELECT * FROM updates;
（online demo）
[注意，在inventory和bill_products表中复制传递信息不是很好的数据库设计。您可能应该忽略bill_prodcts.delivered列，而是每次需要时都计算各自库存条目的总和。