postgresql - 具有参数和dblink的postgres函数

我请求您帮助我发送搜索参数。但问题是，这个函数是通过一个DBlink执行的，我需要传递2个值作为搜索参数，并且在执行时不发送值。
这是不带dblink的函数：

`CREATE OR REPLACE FUNCTION sp_get_afil(i_pers_codigo VARCHAR, i_empr_codigo
 VARCHAR) RETURNS VARCHAR AS $$
 DECLARE
    v_pers_codigo VARCHAR;
 BEGIN
    select ro.pers_codigo
    into v_pers_codigo
    from unv_roles_personas ro, unv_personas p1, unv_personas p
    where ro.rope_vigente = 'S'
    and ro.empr_codigo = p1.empr_codigo
    and ro.tiro_codigo = 'AFIL'
    and ro.pers_codigo = p1.pers_codigo
    and p1.empr_codigo = p.empr_codigo
    and p1.pers_estado_cae = p.pers_estado_cae
    and p1.pers_numero_cae = p.pers_numero_cae
    and p.empr_codigo = i_empr_codigo
    and p.pers_codigo = i_pers_codigo;

    RETURN v_pers_codigo;

END;
$$ LANGUAGE plpgsql;`

它的工作没有问题，这里的功能与dblink相同，但dblink不工作：

`CREATE OR REPLACE FUNCTION sp_get_afil(i_pers_codigo VARCHAR, i_empr_codigo
 VARCHAR) RETURNS VARCHAR AS $$
 DECLARE
    v_pers_codigo VARCHAR;
    v_empr_codigo VARCHAR;
    vr_pers_codigo VARCHAR;
BEGIN
    select t1.rop_codigo
    into vr_pers_codigo
    from dblink('dbname = usuarios host=100.1.1.138 port=5432 ',
                'select ro.pers_codigo
                from unv_roles_personas ro, unv_personas p1, unv_personas p
                where ro.rope_vigente = ''S''
                and ro.empr_codigo = p1.empr_codigo
                and ro.tiro_codigo = ''AFIL''
                and ro.pers_codigo = p1.pers_codigo
                and p1.empr_codigo = p.empr_codigo
                and p1.pers_estado_cae = p.pers_estado_cae
                and p1.pers_numero_cae = p.pers_numero_cae
                and p.empr_codigo = '||quote_literal ($1)||'
                and p.pers_codigo = '||quote_literal ($2)||'')
     as t1 (rop_codigo character varying);

     RETURN vr_pers_codigo;

 END;
 $$ LANGUAGE plpgsql;`

在执行时，它返回一个空值，这不会生成我需要的视图。我做得不好，我已经调查过了，但我已经陷入了僵局。

最佳答案

试试这个：

CREATE OR REPLACE FUNCTION sp_get_afil(i_pers_codigo VARCHAR, i_empr_codigo
 VARCHAR) RETURNS VARCHAR AS $$
 DECLARE
    v_pers_codigo VARCHAR;
    v_empr_codigo VARCHAR;
    vr_pers_codigo VARCHAR;
BEGIN
    select t1.rop_codigo
    into vr_pers_codigo
    from dblink('dbname = usuarios host=100.1.1.138 port=5432 ',
                format('select ro.pers_codigo
                from unv_roles_personas ro, unv_personas p1, unv_personas p
                where ro.rope_vigente = ''S''
                and ro.empr_codigo = p1.empr_codigo
                and ro.tiro_codigo = ''AFIL''
                and ro.pers_codigo = p1.pers_codigo
                and p1.empr_codigo = p.empr_codigo
                and p1.pers_estado_cae = p.pers_estado_cae
                and p1.pers_numero_cae = p.pers_numero_cae
                and p.empr_codigo = %L
                and p.pers_codigo = %L',i_pers_codigo,i_empr_codigo))
     as t1 (rop_codigo character varying);

     RETURN vr_pers_codigo;

 END;
 $$ LANGUAGE plpgsql;