我正在尝试检查要插入系统的房间在该日期是否已出租。我已经考虑过计算与房间号和日期匹配的行,然后回滚事务。但即使我更改了代码以引发用户定义的异常,我仍收到以下错误:
ERROR: cannot begin/end transactions in PL/pgSQL
HINT: Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT: PL/pgSQL function "checkRoom"() line 17 at SQL statement
CREATE OR REPLACE FUNCTION "checkRoom"() RETURNS TRIGGER AS
$BODY$
DECLARE
counter integer;
BEGIN
SELECT COUNT("num_sesion")
FROM "Sesion"
INTO counter
WHERE "Room_Name"=NEW."Room_Name" AND "Date"=NEW."Date";
IF (counter> 0) THEN -- Probably counter>1 as it's triggered after the transaction..
raise notice 'THERE'S A ROOM ALREADY!!';
raise exception 'The room is rented at that date';
END IF;
RETURN new;
EXCEPTION
WHEN raise_exception THEN
ROLLBACK TRANSACTION;
RETURN new;
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF;
然后我创建触发器:
CREATE TRIGGER "roomOcupied" AFTER INSERT OR UPDATE OF "Room_Name", "Date"
ON "Sesion" FOR EACH ROW
EXECUTE PROCEDURE "checkRoom"();
距离我上次接触 SQL 已经过去了 2 年,plsql 和 plpgsql 之间的变化让我抓狂。
触发函数存在几个问题:
-
Use IF EXISTS (...) THEN https://stackoverflow.com/questions/11892233/pl-pgsql-checking-if-a-row-exists-select-into-boolean/11892796#11892796而不是计算所有出现的次数。更快、更简单。看:
- PL/pgSQL 检查行是否存在 https://stackoverflow.com/questions/11892233/pl-pgsql-checking-if-a-row-exists/11892796#11892796
-
有触发功能AFTER
INSERT OR UPDATE
可以直接返回NULL
. RETURN NEW
仅与名为的触发器相关BEFORE
. 手册 https://www.postgresql.org/docs/current/trigger-definition.html:
对于操作后触发的行级触发器,返回值将被忽略,因此它们可以返回NULL
.
单引号不平衡。
As @Pavel 解释道 https://stackoverflow.com/a/16700934/939860,您无法从 plpgsql 函数内控制事务。任何未处理的异常都会迫使您的整个事务自动回滚。所以,只需删除EXCEPTION
block.
你的假设触发器被重写:
CREATE OR REPLACE FUNCTION check_room()
RETURNS TRIGGER AS
$func$
BEGIN
IF EXISTS (
SELECT FROM "Sesion" -- are you sure it's not "Session"?
WHERE "Room_Name" = NEW."Room_Name"
AND "Date" = NEW."Date") THEN
RAISE EXCEPTION 'The room is rented at that date';
END IF;
RETURN NULL;
END
$func$ LANGUAGE plpgsql;
A BEFORE
触发更有意义。
But a UNIQUE INDEX ON ("Room_Name", "Date")
也会做同样的事情,而且效率更高。然后,任何违反的行都会引发重复键异常并回滚事务(除非捕获并处理)。在现代 Postgres 中,你可以选择跳过或转移这样的INSERT
尝试与INSERT ... ON CONFLICT ...
. See:
- 如何在 PostgreSQL 中进行 UPSERT(合并、插入...重复更新)? https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql/17267423#17267423
高级用法:
- 如何在 PostgreSQL 中使用 RETURNING 和 ON CONFLICT? https://stackoverflow.com/questions/34708509/how-to-use-returning-with-on-conflict-in-postgresql/42217872#42217872
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)