Pessoal,
tenho uma função que executa uma consulta e retorna o resultado. Executando
somente a consulta leva em torno de 120ms, mas executando a função leva em
torno de 12000ms, o que pode ser?
A função:
CREATE OR REPLACE FUNCTION registro_ol_selecionar(integer, integer,
integer, integer, character varying, integer, character varying, integer,
text, text, text, text, text, date, time without time zone, integer, text)
RETURNS SETOF type_registro_ol AS
$BODY$
DECLARE query record;
BEGIN
FOR query in
SELECT
a.regol_codigo,
a.regol_codempresa,
a.regol_codcto,
a.regol_codtabela,
a.regol_numlacre,
a.regol_codagencia,
a.regol_codbarras,
a.regol_codmov,
a.regol_responsavel,
a.regol_processo,
a.regol_fase,
a.regol_evento,
a.regol_proxresp,
a.regol_dtcad,
a.regol_hrcad,
a.regol_status,
a.regol_mensagem
FROM
registro_ol a
INNER JOIN usuario_ol b ON(a.regol_codcto = b.usuol_codcto)
WHERE
(a.regol_codigo = $1 or $1 is null)
AND (a.regol_codcto = $3 or $3 is null)
AND (a.regol_fase = $11 or $11 is null)
AND (a.regol_status = $16 or $16 is null)
AND (a.regol_codempresa = $2 or $2 is null)
AND (a.regol_codtabela = $4 or $4 is null)
AND (a.regol_numlacre = $5 or $5 is null)
AND (a.regol_codagencia = $6 or $6 is null)
AND (a.regol_codbarras = $7 or $7 is null)
AND (a.regol_codmov = $8 or $8 is null)
AND (a.regol_responsavel = $9 or $9 is null)
AND (a.regol_processo = $10 or $10 is null)
AND (a.regol_evento = $12 or $12 is null)
AND (a.regol_proxresp = $13 or $13 is null)
AND (a.regol_dtcad = $14 or $14 is null)
AND (a.regol_hrcad = $15 or $15 is null)
AND (a.regol_mensagem = $17 or $17 is null)
AND (b.usuol_status = 't')
AND (
CASE
WHEN a.regol_fase = 'DISTRI' THEN
CASE
WHEN (
SELECT x.regol_status FROM registro_ol x
WHERE (x.regol_codbarras = a.regol_codbarras)
AND (x.regol_codcto = a.regol_codcto)
AND (x.regol_codtb011 = a.regol_codtb011)
AND (x.regol_fase = 'RECEXP') AND ((x.regol_dtcad+x.regol_hrcad) <=
(a.regol_dtcad+a.regol_hrcad))
ORDER BY x.regol_dtcad DESC, x.regol_hrcad DESC LIMIT 1
) = 2 THEN 1 = 1
ELSE 1 = 0
END
ELSE 1 = 1
END
)
ORDER BY a.regol_codmov,a.regol_dtcad
LIMIT 450
LOOP
RETURN NEXT query;
END LOOP;
END
$BODY$
LANGUAGE plpgsql IMMUTABLE;
Query que chama a função:
select * from
registro_ol_selecionar((NULL),(NULL),(249),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(E'RECEXP'),(NULL),(NULL),(NULL),(NULL),(1),(NULL))
[]s
Danilo
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
