是否可以取消用户定义函数中之前的操作?
例如:
CREATE OR REPLACE FUNCTION transact_test () RETURNS BOOLEAN
AS $$
BEGIN
UPDATE table1 SET ...
UPDATE table2 SET ...
IF some_condition THEN
--Here is possible to cancel all above operations?
RETURN FALSE;
END IF;
RETURN TRUE;
END;
$$
LANGUAGE plpgsql;
到目前为止,这两个答案都是不正确的。
如果您尝试开始交易或使用SAVEPOINT
在 plpgsql 函数中,您会收到如下错误消息:
ERROR: cannot begin/end transactions in PL/pgSQL
HINT: Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT: PL/pgSQL function "f_savepoint" line 6 at SQL statement
如果你尝试一个SAVEPOINT
在一个普通的 SQL 函数中:
ERROR: SAVEPOINT is not allowed in a SQL function
CONTEXT: SQL function "f_savepoint2" during startup
按照错误消息的指示,使用BEGIN block改为在 plpgsql 函数内。您的演示可能如下所示:
CREATE OR REPLACE FUNCTION transact_test(boolean)
RETURNS boolean AS
$func$
BEGIN -- start a nested BEGIN block
UPDATE t SET i = i+1 WHERE i = 1;
UPDATE t SET i = i+1 WHERE i = 3;
IF $1 THEN
RAISE EXCEPTION 'foo'; -- cancels all of the above
END IF;
RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
-- do nothing
END
$func$ LANGUAGE plpgsql;
-> SQLfiddle展示这一切。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)