Обсуждение: [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
The following bug has been logged on the website:
Bug reference: 14464
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
This list is intended for reporting bugs in PostgreSQL. This request for help is not that. Please choose a more appropriate list - pgsql-general@postgresql.org suffices as a catch-all.
That said, you'll likely find improvement by choosing the correct language to write your function in. Namely, choose SQL unless and until you are forced to choose pl/pgsql. As you are simply embedding a single SELECT query you do not use any features that are only present in pl/pgsql.
David J.