The following bug has been logged on the website:
Bug reference: 14464
Logged by: Sys Pegasus
Email address: syspegasus@gmail.com
PostgreSQL version: 9.6.1
Operating system: Windows 10 64bits
Description:
Hi, I'm with problems about functions.
When I execute a select with Function is very slow, and when I execute
repeating the code of the existing function inside my SQL is faster.
I need to centralize my code, because of that I'm using functions, but is
very very slow..
Please how can I use Function and become faster? I need to centralize the
SQL.
See my examples...
MY FUNCTION:
CREATE OR REPLACE FUNCTION sp_valor (
parcodfilial numeric,
parcodpedido numeric,
parcoditem numeric,
parcodproduto numeric,
partipodif char
)
RETURNS numeric AS
$body$
DECLARE
VALOR NUMERIC;
BEGIN
SELECT COALESCE(SUM(A.VLRUNIT),0) INTO VALOR
FROM FAT0013 A
WHERE A.CODFILIAL = PARCODFILIAL
AND A.CODPEDIDO = PARCODPEDIDO
AND A.CODITEM = PARCODITEM
AND A.CODPRODUTO = PARCODPRODUTO
AND A.TIPODIF = PARTIPODIF;
RETURN VALOR;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE;
SQL FAST (00:17):
SELECT A.CODFILIAL,
A.CODPEDIDO,
(SELECT COALESCE(SUM(X.VLRUNIT),0) VALOR
FROM FAT0013 X
WHERE X.CODFILIAL = A.CODFILIAL
AND X.CODPEDIDO = A.CODPEDIDO
AND X.CODITEM = A.CODITEM
AND X.CODPRODUTO = A.CODPRODUTO
AND X.TIPODIF = 'A')
FROM FAT0002 A
SQL SLOW (04:54):
SELECT A.CODFILIAL,
A.CODPEDIDO,
SP_VALOR(A.CODFILIAL, A.CODPEDIDO, A.CODITEM, A.CODPRODUTO,
'A')
FROM FAT0002 A
Thanks.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
