gpt4 book ai didi

sql - 触发函数内基于 NEW.id 的动态 SUM

转载 作者:行者123 更新时间:2023-11-29 13:25:44 25 4
gpt4 key购买 nike

现在我正在编写一个具有 PostgreSQL 数据库的应用程序。我的任务是根据 new.id 编写将执行 SQL 语句的触发器。

只有一个问题,触发器不执行查询。它只是什么都不插入。

DB output

CREATE OR REPLACE FUNCTION bill_creator()
RETURNS TRIGGER AS $build_bill$
declare
summ INTEGER;
BEGIN
SELECT SUM(ITEMS.PRICE) INTO summ
FROM ITEMS
INNER JOIN PARTS
ON PARTS.ITEM_ID = ITEMS.ID AND PARTS.ORDER_ID = NEW.ID;

INSERT INTO BILLS (
created,
summary,
cashier_id,
customer_id,
order_id,
created_at,
updated_at,
options
)
VALUES (
current_date,
summ,
1,
new.customer_id,
new_id,
current_timestamp,
current_timestamp,
'None'
);
RETURN NEW;
END;
$build_bill$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER build_bill AFTER INSERT ON ORDERS
FOR EACH ROW EXECUTE PROCEDURE bill_creator();

我使用的是 Rails,所以表是由 ActiveRecords 创建的,但它们存储在模式中。

create_table "bills", force: :cascade do |t|
t.datetime "created"
t.string "options"
t.integer "summary"
t.integer "cashier_id"
t.integer "customer_id"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.integer "order_id"
end

create_table "orders", force: :cascade do |t|
t.integer "customer_id"
t.integer "waiter_id"
t.integer "manager_id"
t.integer "chef_id"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end

create_table "parts", force: :cascade do |t|
t.integer "order_id"
t.integer "item_id"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end

create_table "items", force: :cascade do |t|
t.integer "price"
t.string "description"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
t.integer "menu_id"
end

add_index "parts", ["item_id"], name: "index_parts_on_item_id", using: :btree
add_index "parts", ["order_id"], name: "index_parts_on_order_id", using: :btree

最佳答案

你的功能看起来基本不错。我简化并修复了一些问题:

CREATE OR REPLACE FUNCTION bill_creator()
RETURNS TRIGGER AS
$build_bill$
BEGIN
INSERT INTO bills (
created,
summary,
cashier_id,
customer_id,
order_id,
created_at,
updated_at,
options
)
SELECT
current_date,
SUM(i.price)
1,
NEW.customer_id,
NEW.id -- ! 'new_id was undefined !
current_timestamp,
current_timestamp,
'None'
FROM parts p
JOIN items i ON p.item_id = i.id
WHERE p.order_id = NEW.id;

RETURN NULL;
END
$build_bill$ LANGUAGE plpgsql;

您不需要单独的 SELECT 和变量赋值。将其集成到单个 INSERT 中,速度更快。结果是一样的,聚合函数 sum() 总是返回一行,即使什么也没找到。

new_id 是未定义的,我假设你的意思是 NEW.id

我使用 RETURN NULL 因为,per documentation:

The return value of a row-level trigger fired AFTER [...] is always ignored; it might as well be null.

不确定为什么要createdcreated_at。两者都是类型定义为 t.datetime,应转换为 Postgres timestamp。也不知道为什么你写 current_date 而不是 current_timestamp 到第一个。

但是,您插入的summary 为NULL 并不奇怪。在 orders 中插入一行后,parts 中不能有相关条目,但假设参照完整性。因此,此时向 bill 中插入总和的想法注定会失败。您必须使用插入/更新/删除的每个新部分 UPDATE 该列。这是一袋跳蚤,您必须处理这些跳蚤才能使总和保持最新 - 或多或少......

我建议您完全删除 bill.summary 列,并改用动态计算总和的 VIEW。可以是 MATERIALIZED VIEW 以避免重复聚合。

关于sql - 触发函数内基于 NEW.id 的动态 SUM,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34111448/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com