I have a problem creating a function . I'm 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 comparing with numeric type in query within 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 = variavel1 ...
Just because% of providers is integer !!!!
I have tried this cast within the function:
inner JOIN prestadores m on m.id= variavel1::integer
But there it is also wrong because it does not recognize the id
that I need to pass by parameter. I can not change the function to receive only numbers because there may be cases in my application that use other ids from other tables (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;
Function execution
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 case the variable c.id_medico
is getting the m
of the table that I will reference in the database, according to what the user is choosing ...