Creating Function Postgresql

Question

I’m having a problem creating a Function. I am pulling its parameters from my java application, the problem occurs when pulling the parameters ( variavel1, and variavel2) because they are coming from the application as text. An error occurs when I compare with a numeric type in query within the function:

 inner JOIN prestadores m on m.id= variavel1

Error

ERROR: Operator does not exist: integer = text LINE 12: ...
Inner JOIN providers m on m.id= variable1...

Precisely because the id of providers is integer !!!!

I’ve tried to do that cast within the function:

inner JOIN prestadores m on m.id= variavel1::integer  

But then he’s wrong too because he doesn’t recognize the c.id_medico I need to pass by parameter. I can’t change the function to only receive numbers because there may be cases in my application that use other table ids (with other types):

Function

CREATE OR REPLACE FUNCTION public.rel_repasse_solicitante(data1 date, data2 date, idcorp integer, titulo text, variavel1 text, variavel2 text)
  RETURNS SETOF record AS

$BODY$
BEGIN
 RETURN QUERY select   data1 as inicio, 
                        data2 as fim, p.nome as paciente, 
                        titulo as titulo, pl.nome as plano, m.nome as medico,
                        c.data_prescricao AS data_atendimento, c.codigo_tiss, 
                        c.codigo_tiss || ' - ' || t.descri as descri, c.conta, c.id_pagamento_medico, c.guia, 
                        5 as taxa, c.quant, (c.quant * 5) as valor_total 
                        from contas c 
                        inner join cadastro_pessoa_fisica p on p.id=c.id_paciente 
                        left outer join pagamento_medico pg on pg.id=c.id_pagamento_medico 
                        inner join plano_convenio pl on pl.id=c.id_plano 
                        inner join guia_consulta_tiss g on g.id=c.guia 
                        inner JOIN prestadores m on m.id= variavel1
                        inner JOIN prestadores co on co.id=pl.id_convenio 
                        inner join empresas e on e.id=co.id_empresa 
                        left outer join tabela_amb t on t.codigo=c.codigo_tiss and
                        variavel2 and t.ativo=true and t.id_tabela=pl.id_tabela_amb 
                        where p.id_corp=idcorp and 
                        c.data_prescricao >=data1 
                        and c.data_prescricao <=data2 
                        and (c.tipo=3 or c.tipo > 4) and c.codigo_tiss != '50101010' 
                        and c.codigo_tiss != '60101010' and c.codigo_tiss != '20010010' 
                        and c.codigo_tiss != '10101012' and c.codigo_tiss != '20101012' 
                        and c.codigo_tiss != '30101012' and c.codigo_tiss != '00010014' 
                        and c.codigo_tiss != '40101010' and c.codigo_tiss != '20010010' 
                        and c.codigo_tiss != '20101011' 
                        order by pg.id, m.nome, c.data_prescricao, paciente;
 RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public.rel_repasse_solicitante(data1 date, data2 date, idcorp integer, titulo text, variavel1 text, variavel2 text)
  OWNER TO postgres;

Performance of the function

select * from rel_repasse_solicitante('2016-01-01', '2016-12-09',1, 
'PREVIA DO REPASSE DE BONUS PARA O MEDICO EXECUTANTE', 'c.id_medico', 
' c.data_prescricao >=2016-01-01 and c.data_prescricao <=2016-12-09 and 
c.data_recebimento notnull and c.id_pagamento_medico isnull and c.ativo=true') as 
(inicio date, fim date, paciente character varying, titulo text, plano character varying, 
medico character varying, data_atendimento date, codigo_tiss character varying, descri text, conta integer, 
id_pagamento_medico integer, guia integer, taxa integer, quant numeric, valor_total numeric)

Java call

if (Integer.parseInt(request.getParameter("tipo")) == 191 || Integer.parseInt(request.getParameter("tipo")) == 192 || Integer.parseInt(request.getParameter("tipo")) == 193) {
                relatorio = caminho + "Rel_Repasse_Solicitante.jasper";
                resportStream = getServletConfig().getServletContext().getResourceAsStream(relatorio);
                String titulo="REPASSE DE BONUS";
                String m = "c.id_medico";
                if (Integer.parseInt(request.getParameter("tipo")) == 192) {
                        titulo = "PREVIA DO REPASSE DE BONUS";
                    }else
                    if (Integer.parseInt(request.getParameter("tipo")) == 193) {
                        titulo = "PENDENCIA DE REPASSE DE BONUS";
                    }

                if(request.getParameter("tipomedico").equals("1")){
                        //opcao para medico executante
                        titulo+=" PARA O MEDICO EXECUTANTE";

                    }else
                     if(request.getParameter("tipomedico").equals("2")){
                        //opcao para medico solicitante
                        titulo+=" PARA O MEDICO SOLICITANTE";
                        m= "g.id_solicitante";
                    }else{
                         //opcao para medico autorizador
                         titulo+=" PARA O MEDICO AUTORIZADOR";
                          m= "g.id_medico_autorizador";
                     } 
                conexao.executeSQL("select * from rel_repasse_solicitante("
                        + "'"+request.getParameter("datainicial")+"', "
                        + "'"+request.getParameter("datafinal")+"',"+idcorp+", "
                        + "'"+titulo+"', '"+m+"', '"+request.getParameter("tipo")+"',"
                        + ""+request.getParameter("idmedico")+") as "
                        + "(inicio date, fim date, paciente character varying, "
                        + "titulo text, plano character varying, "
                        + "medico character varying, data_atendimento date, "
                        + "codigo_tiss character varying, descri text, "
                        + "conta integer, id_pagamento_medico integer, "
                        + "guia integer, taxa integer, quant numeric, "
                        + "valor_total numeric)");

In the case of the variable m is receiving the ID of the table I will reference in the database, according to what the user is choosing...

Answer 1

You can use Callablestatement.

CallableStatement statement = connection.prepareCall(" { call function( ?, ?, ? ) } ");
statement.setInt(1, value);
statement.setInt(2, value);
statement.setInt(3, value);
statement.execute();
statement.close();

reference: https://stackoverflow.com/questions/17435060/call-a-stored-function-on-postgres-from-java