背景
PostgreSQL 9.5开始支持plpgsql函数断言(assert)语法, 断言某个条件是否为真, 如果不为真 则抛出ASSERT_FAILURE异常, 如果条件语句本身执行异常 则抛出普通的异常信息.
默认配置参数plpgsql.check_asserts=on, 表示支持assert语句, 如果为off, 则不评估assert的断言条件(相当于旁路assert条件)
详细说明参考: https://www.postgresql.org/docs/14/plpgsql-errors-and-messages.html
43.9.2. Checking Assertions
The ASSERT statement is a convenient shorthand for inserting debugging checks into PL/pgSQL functions.
ASSERT condition [ , message ];
The condition is a Boolean expression that is expected to always evaluate to true; if it does, the ASSERT statement does nothing further. If the result is false or null, then an ASSERT_FAILURE exception is raised. (If an error occurs while evaluating the condition, it is reported as a normal error.)
If the optional message is provided, it is an expression whose result (if not null) replaces the default error message text “assertion failed”, should the condition fail. The message expression is not evaluated in the normal case where the assertion succeeds.
Testing of assertions can be enabled or disabled via the configuration parameter plpgsql.check_asserts, which takes a Boolean value; the default is on. If this parameter is off then ASSERT statements do nothing.
Note that ASSERT is meant for detecting program bugs, not for reporting ordinary error conditions. Use the RAISE statement, described above, for that.
例子:
DO $$
BEGIN
ASSERT (SELECT has_schema_privilege('flipr', 'usage'));
END $$;
DO $$
DECLARE
result varchar;
BEGIN
result := (SELECT name FROM flipr.pipelines WHERE id = 1);
ASSERT result = 'Example';
END $$;
例如, sqitch用到断言来处理数据迁移的校验工作.
https://sqitch.org/docs/manual/sqitchtutorial/
Sqitch is a database change management application. What makes it different from your typical migration-style approaches? A few things.