使用current_setting()检查值 [英] Check for value with current_setting()
问题描述
我正在尝试使用 current_setting()
。
我想到了这个:
CREATE OR REPLACE FUNCTION process_audit() RETURNS TRIGGER AS $audit$
DECLARE
user_id integer;
BEGIN
BEGIN
user_id := current_setting('hws.current_user_id');
EXCEPTION WHEN OTHERS THEN
user_id := NULL;
END;
...
RETURN NULL;
END;
$audit$ LANGUAGE plpgsql;
通过以下方式设置设置:
The setting is set via:
SELECT set_config('hws.current_user_id', '5', true); -- true = local setting -> only visible in current transaction
问题是, current_setting()如果值无效,code>会引发异常。我不想使用
EXCEPTION
,因为我读到异常块很昂贵。
The problem is, that current_setting()
throws an exception if the value is not valid. I don't want to use EXCEPTION
because I read that exception blocks are expensive.
有没有办法
Btw:我也尝试从 pg_settings
中读取内容,但是那
Btw: I also tried to read from pg_settings
but that doesn't seem to work with local settings.
推荐答案
9.6及更高版本:
PostgreSQL(9.6+)支持 current_setting('setting_name','t')
来获取设置并返回 NULL
(如果未设置)。您可以将其与 coalesce
结合使用以提供默认值。
9.6 and newer:
PostgreSQL (9.6+) supports current_setting('setting_name', 't')
to fetch a setting and return NULL
if it's unset. you can combine this with coalesce
to supply a default.
每个问题,您都可以使用 plpgsql
函数,该函数使用 BEGIN ... EXCEPTION
处理程序,如果您不介意性能下降和笨拙。但是没有内置的支持。
Per the question, you can do it with a plpgsql
function that uses a BEGIN ... EXCEPTION
handler, if you don't mind the performance hit and clumsiness. But there's no built-in support.
这篇关于使用current_setting()检查值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!