在存储过程中设置自定义选项 [英] Setting a customized option in a stored procedure
问题描述
我试图在存储过程中设置自定义选项,但它存储的是变量名而不是变量的内容.
I'm trying to set a custom option in a stored procedure, but it is storing the variable name and not contents of the variable.
CREATE OR REPLACE FUNCTION set_user(_user_id bigint, is_local boolean default true) returns void AS $$
BEGIN
SET my.user_id TO _user_id;
END;
$$ LANGUAGE PLPGSQL;
select set_user(1);
select current_setting('my.user_id');
current_setting
-----------------
_user_id
(1 row)
我希望 current_setting
返回 1
,而不是字符串值 "_user_id"
.
I expect current_setting
to return 1
, not the string value "_user_id"
.
推荐答案
第一个解决方案
SET
的语法是:
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value |'value' | DEFAULT }
其中 value
是给定 configuration_parameter
的新值.
where value
is the new value for a given configuration_parameter
.
为了给存储在 _user_id
变量中的值赋值,你需要生成一个动态命令,然后 EXECUTE
它.
In order to assign a value stored in _user_id
variable, you need to generate a dynamic command and then EXECUTE
it.
这将是这样做的方式:
CREATE OR REPLACE FUNCTION set_user(_user_id bigint, is_local boolean default true)
RETURNS void
LANGUAGE PLPGSQL
AS $$
BEGIN
EXECUTE 'SET my.user_id TO ' || quote_nullable(_user_id);
END;
$$;
附加 SQL Fiddle 链接以进行测试.
Attaching SQL Fiddle link for testing purposes.
注意:
quote_nullable()
如果输入参数为空,函数将返回NULL
.在您的情况下可能没有必要.
quote_nullable()
function would returnNULL
if the input argument is null. It may not be necessary in your case.
您也可以使用 set_config()
函数如@a_horse_with_no_name 所述.您的函数将如下所示:
You could also achieve the same thing with set_config()
function as @a_horse_with_no_name noted. Your function would then look like that:
CREATE OR REPLACE FUNCTION set_user(_user_id bigint, is_local boolean default true)
RETURNS void
LANGUAGE PLPGSQL
AS $$
BEGIN
PERFORM set_config('my.user_id', _user_id::TEXT, false);
END;
$$;
附加 SQL Fiddle 链接以进行测试.
Attaching SQL Fiddle link for testing purposes.
注意:
- 您必须将第二个参数显式转换为 varchar 类型
PERFORM
用于计算表达式并丢弃结果,因为它不需要- 您也可以在这里使用
quote_nullable()
函数
这篇关于在存储过程中设置自定义选项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!