Thread: [BUGS] BUG #14464: Problems about FUNCTIONS

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