在函数中不工作的Postgres会话变量

Question

我在postgres函数中设置了一个会话变量，并且没有设置值。任何帮助都是非常感谢的。提前谢谢。我使用的是"PostgreSQL 10.6，由Visual C++ build 1800编译，64位“。

我的代码如下:函数：

CREATE FUNCTION set_rp_vals(iv_rp_company varchar, iv_rp_portfolio varchar)
RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$

DECLARE

l_retval integer;

BEGIN

    l_retval := 1;

    RAISE NOTICE '1.iv_rp_company: >>> %', iv_rp_company;
    RAISE NOTICE '2.iv_rp_portfolio: >>> %', iv_rp_portfolio;

    --set the session variable
    set rp.company   = iv_rp_company;
    set rp.portfolio = iv_rp_portfolio;

    RETURN l_retval;

    EXCEPTION
        WHEN OTHERS THEN
            RETURN 9;
    END;

$function$
;

函数调用：

SELECT set_rp_vals(iv_rp_company := 'COMPAN',iv_rp_portfolio := 'PORTOF');

-检索会议变量：

select 
current_setting('rp.company') as company,
current_setting('rp.portfolio') as portfolio;

上述查询返回的值：

Answer 1

为此，我将使用set_config()函数：

CREATE OR REPLACE FUNCTION set_rp_vals(iv_rp_company varchar, iv_rp_portfolio varchar)
RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$

DECLARE

l_retval integer;

BEGIN

    l_retval := 1;

    RAISE NOTICE '1.iv_rp_company: >>> %', iv_rp_company;
    RAISE NOTICE '2.iv_rp_portfolio: >>> %', iv_rp_portfolio;

    --set the session variable
    perform set_config('rp.company', iv_rp_company, false);
    perform set_config('rp.portfolio', iv_rp_portfolio, false);

    RETURN l_retval;

    EXCEPTION
        WHEN OTHERS THEN
            RETURN 9;
    END;

$function$
;
CREATE FUNCTION

用您的值执行：

SELECT set_rp_vals(iv_rp_company := 'COMPAN',iv_rp_portfolio := 'PORTOF');            

NOTICE:  1.iv_rp_company: >>> COMPAN
NOTICE:  2.iv_rp_portfolio: >>> PORTOF
┌─────────────┐
│ set_rp_vals │
├─────────────┤
│           1 │
└─────────────┘
(1 row)

select                                                                                
current_setting('rp.company') as company,
current_setting('rp.portfolio') as portfolio;

┌─────────┬───────────┐
│ company │ portfolio │
├─────────┼───────────┤
│ COMPAN  │ PORTOF    │
└─────────┴───────────┘
(1 row)