将本地设置为以前存储的值

Question

我想暂时更改我的PL/pgSQL函数中的lock_timeout，我认为它会像这样简单：

CREATE OR REPLACE FUNCTION public.myfunction()
RETURNS void AS
$body$
DECLARE
l_tmp_lock_timeout TEXT;
BEGIN
-- We want this to bail out when no lock is gained
SHOW lock_timeout INTO l_tmp_lock_timeout;
SET LOCAL lock_timeout TO '10s';
RAISE NOTICE 'Local lock timeout set to 10s, was %', l_tmp_lock_timeout;
-- ... do stuff ...
-- and reset the lock timeout
SET LOCAL lock_timeout TO l_tmp_lock_timeout;
RAISE NOTICE 'Local lock timeout set to %', l_tmp_lock_timeout;
RETURN;    
END;
$body$
LANGUAGE 'plpgsql'

但这给了我这个结果：

NOTICE:  Local lock timeout set to 10s, was 0
ERROR:  invalid value for parameter "lock_timeout": "l_tmp_lock_timeout"
CONTEXT:  SQL statement "SET LOCAL lock_timeout TO l_tmp_lock_timeout"
PL/pgSQL function myfunction() line 48 at SQL statement

如何正确存储和重置该lock_timeout设置？

Answer 1

像SET这样的实用程序语句不能与参数一起使用。

你必须使用动态SQL，如

EXECUTE format('SET lock_timeout = %L', l_tmp_lock_timeout);

或使用

SELECT set_config('lock_timeout', l_tmp_lock_timeout, FALSE);