From: | Sistema Ágile <syspegasus(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Fwd: Problems about FUNCTIONS |
Date: | 2016-12-13 16:35:20 |
Message-ID: | CADt=wv24cdaOG9v4FRLPhTeYa9KdQd8YeLb+tngZs780HFgNXA@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
From | Date | Subject | |
---|---|---|---|
Next Message | syspegasus | 2016-12-13 16:42:35 | BUG #14464: Problems about FUNCTIONS |
Previous Message | Análise Sistema Ágile | 2016-12-13 16:32:16 | Problems about FUNCTIONS |