From: | syspegasus(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14464: Problems about FUNCTIONS |
Date: | 2016-12-13 16:42:35 |
Message-ID: | 20161213164235.14896.94924@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14464
Logged by: Sys Pegasus
Email address: syspegasus(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-12-13 16:45:31 | Re: BUG #14463: refcursor cann't used with array or variadic parameter? |
Previous Message | Sistema Ágile | 2016-12-13 16:35:20 | Fwd: Problems about FUNCTIONS |