BUG #14464: Problems about FUNCTIONS

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.

Responses

Browse pgsql-bugs by date

  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