Problems about FUNCTIONS

From: Análise Sistema Ágile <analise(at)sistemaagile(dot)com(dot)br>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problems about FUNCTIONS
Date: 2016-12-13 16:32:16
Message-ID: CADt=wv10wYgB1JujoYxkgSRC0Mzxoge7CPW=GSeFfrYwgBWyYw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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.

--
Ágile Sistemas - (15) 3282-6925
www.sistemaagile.com.br

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sistema Ágile 2016-12-13 16:35:20 Fwd: Problems about FUNCTIONS
Previous Message Pavel Stehule 2016-12-13 16:16:43 Re: BUG #14463: refcursor cann't used with array or variadic parameter?