postgres functions with parameters and dblink

Question

I ask for your help with a function to which I need to send search parameters. But the problem is that the function is executed through a DBlink, and I need to pass 2 values as a search parameter, and when executing it does not send the values.

This is the function without 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;`

It works without problems, and here the same function but with dblink, which does not work:

`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;`

When executing it, it returns a null value and this does not generate the view I need. I'm doing badly, I've investigated, but I'm already in a deadlock.

Vao Tsun · Accepted Answer · 2017-12-29 13:00:38Z

5

try this:

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;

answered Dec 29, 2017 at 13:00

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Darwin Delgado Over a year ago

Thank you, unfortunately it is the same result, it returns the null value.

Vao Tsun Over a year ago

hm. without reproducible env, Its hard to say why you have null then

KSN Over a year ago

Hey guys, how to parameterize the host name or any variable in where clause of the sql statement?

Darwin Delgado · Accepted Answer · 2018-01-02 21:06:36Z

Thanks Vao Tsun, change the perspective and exchange the function on the side of the database to which I access.

enter code here

`select t1.a_ctac_correlativo,
   t1.a_pers_codigo_socadm,
   b.pers_codigo_scd,
   b.pers_sigla,
   b.pers_nombre_completo nombre_soc,
   t1.recl_correlativo,
   d.pers_codigo,
   d.pers_nombre_completo,
   d.pers_nombre_fantasia,
   d.pers_sigla edit_sigla,       
   t1.monto,
   e.tiro_codigo,       
   t1.a_titu_codigo,
   t1.a_ajuc_correlativo
from dblink('dbname = liquidaciones host=100.1.1.138 port=5432 ',
        'select a.ctac_correlativo a_ctac_correlativo,
                a.pers_codigo_socadm a_pers_codigo_socadm,
                (case when a.ctac_correlativo = 1213 then 48 else 
 a.ctac_correlativo end) recl_correlativo,
                sum(redi_monto) monto,
                a.titu_codigo a_titu_codigo,
                a.ajuc_correlativo a_ajuc_correlativo,
                a.pers_codigo a_pers_codigo,
                a.empr_codigo a_empr_codigo
         from redit a
         where a.redi_liquidado =''S''
         and a.redi_correlativo_padre is null
         group by a.ctac_correlativo,
         a.pers_codigo_socadm,
         recl_correlativo,
         a.titu_codigo,
         a.ajuc_correlativo,
         a.pers_codigo,
         a.empr_codigo')
         as t1 (a_ctac_correlativo character varying,
                a_pers_codigo_socadm character varying,
                recl_correlativo character varying,
                monto character varying,
                a_titu_codigo character varying,
                a_ajuc_correlativo character varying,
                a_pers_codigo character varying,
                a_empr_codigo character varying),
    unv_personas b,
    unv_roles_personas e,
    unv_personas d
 where b.pers_codigo = t1.a_pers_codigo_socadm
 and e.empr_codigo = d.empr_codigo
 and e.pers_codigo = d.pers_codigo
 and e.tiro_codigo = 'EDIT'
 and b.pers_codigo = (select sp_get_afil(CASE WHEN t1.a_pers_codigo = 
 '10010407' THEN '10010436' ELSE t1.a_pers_codigo END, t1.a_empr_codigo))
 and t1.a_pers_codigo in (select pers_codigo from unv_roles_personas where 
tiro_codigo = 'MANDAT'
                     INTERSECT
                     select pers_codigo from unv_roles_personas where 
tiro_codigo = 'EDIT')
group by t1.a_ctac_correlativo,
     t1.a_pers_codigo_socadm,
     b.pers_codigo_scd,
     b.pers_sigla,
     nombre_soc,
     t1.recl_correlativo,
     d.pers_codigo,
     d.pers_nombre_completo,
     d.pers_nombre_fantasia,
     edit_sigla,       
     t1.monto,
     e.tiro_codigo,       
     t1.a_titu_codigo,
     t1.a_ajuc_correlativo;`

Collectives™ on Stack Overflow

postgres functions with parameters and dblink

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related