取消用户定义功能中的先前操作

问题描述

是否可以取消用户定义函数中的先前操作?

Is it possible to cancel previous operations in a user defined function?

例如:

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;

推荐答案

到目前为止，这两个答案都不正确.
如果尝试启动事务或在plpgsql函数中使用SAVEPOINT，则会收到如下错误消息:

Both answers so far are incorrect.
If you try to start a transaction or use a SAVEPOINT inside a plpgsql function you get an error message like this:

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

如果您在普通的SQL函数中尝试使用SAVEPOINT:

If you try a SAVEPOINT inside a plain SQL function:

ERROR:  SAVEPOINT is not allowed in a SQL function
CONTEXT:  SQL function "f_savepoint2" during startup

按照错误消息的指示，使用请在plpgsql函数中使用"> BEGIN块.您的演示可能如下所示:

As the error message instructs, use a BEGIN block inside a plpgsql function instead. Your demo could look like this:

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 演示了这一切.

这篇关于取消用户定义功能中的先前操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！