如何动态执行 PostgreSQL RAISE 命令
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11916838/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to execute PostgreSQL RAISE command dynamically
提问by Andrus
How to raise error from PostgreSQL SQL statement if some condition is met?
I tried code below but got error.
如果满足某些条件，如何从 PostgreSQL SQL 语句引发错误？
我尝试了下面的代码，但出现错误。
CREATE OR REPLACE FUNCTION "exec"(text)
RETURNS text AS
$BODY$
BEGIN
EXECUTE ;
RETURN ;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
-- ERROR: syntax error at or near "raise"
-- LINE 1: raise 'test'
SELECT exec('raise ''test'' ') WHERE TRUE
In real application TRUEis replaced by some condition.
在实际应用TRUE中被一些条件所取代。
Update
更新
I tried to extend answer to pass exception message parameters. Tried code below but got syntax error. How to pass message parameters ?
我试图扩展答案以传递异常消息参数。尝试了下面的代码，但出现语法错误。如何传递消息参数？
CREATE OR REPLACE FUNCTION exec(text, variadic )
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
RAISE EXCEPTION , ;
END;
$BODY$;
SELECT exec('Exception Param1=% Param2=%', 'param1', 2 );
回答by Erwin Brandstetter
You cannot call RAISEdynamically (with EXECUTE) in PL/pgSQL - that only works for SQL statements, and RAISEis a PL/pgSQL command.
您不能在 PL/pgSQL 中RAISE动态调用（使用EXECUTE）——它只适用于 SQL 语句，并且RAISE是一个 PL/pgSQL 命令。
Use this simple function instead:
改用这个简单的函数：
CREATE OR REPLACE FUNCTION f_exec(text)
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
RAISE EXCEPTION '%', ;
END;
$BODY$;
Call:
称呼：
SELECT f_exec('My message is empty!');
I wrote more in this related answer on dba.SE: explanation, links, more options for the function, context(including line number) from the function call etc.
我在 dba.SE 上的这个相关答案中写了更多：解释、链接、函数的更多选项、来自函数调用的上下文（包括行号）等。
Additional answer to comment
对评论的补充回答
CREATE OR REPLACE FUNCTION f_exec1(VARIADIC text[])
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
RAISE EXCEPTION 'Reading % % %!', [1], [2], [3];
END;
$BODY$;
Call:
称呼：
SELECT f_exec1('the','manual','educates');
VARIADICis not a data type, but an argument mode.Elements have to be handled like any other array element.
To use multiple variables in a
RAISEstatement, put multiple%into the message text.
VARIADIC不是数据类型，而是参数 mode。必须像处理任何其他数组元素一样处理元素。
要在
RAISE语句中使用多个变量，请将多个%放入消息文本中。
The above example will fail if no $3 is passed to the call. You'd have to assemble a string from the variable number of input elements. Example:
如果没有 $3 传递给调用，上面的示例将失败。您必须从可变数量的输入元素组合一个字符串。例子：
CREATE OR REPLACE FUNCTION f_exec2(VARIADIC _arr text[])
RETURNS void LANGUAGE plpgsql AS
$BODY$
DECLARE
_msg text := array_to_string(_arr, ' and '); -- simple string construction
BEGIN
RAISE EXCEPTION 'Reading %!', _msg;
END;
$BODY$;
Call:
称呼：
SELECT f_exec2('the','manual','educates');
I doubt you need a VARIADICparameter for this at all. Read the manual here.
Instead, define all parameters, maybe add defaults:
我怀疑你VARIADIC根本不需要一个参数。在这里阅读手册。
相反，定义所有参数，也许添加默认值：
CREATE OR REPLACE FUNCTION f_exec3(_param1 text = ''
,_param2 text = ''
,_param3 text = 'educates')
RETURNS void LANGUAGE plpgsql AS
$BODY$
BEGIN
RAISE EXCEPTION 'Reading % % %!', , , ;
END;
$BODY$;
Call:
称呼：
SELECT f_exec3('the','manual','educates');
SELECT f_exec3(); -- defaults kick in
