Re: Decrease the time required function

Karel Riverón wrote:
> I have a PL/pgSQL function that it takes 4 seconds to execute. This is my function:
>
> CREATE OR REPLACE FUNCTION listarcasosrecuperados(escenario_id integer)
> RETURNS SETOF caso_real AS
[...]
> OPEN criterios;
> LOOP FETCH criterios into c;
> IF NOT FOUND THEN
> EXIT;
> ELSE
> sum_impactos := sum_impactos + c.impacto;
> END IF;
> END LOOP;
> CLOSE criterios;

This is very inefficient.
You should use as much SQL as possible:

SELECT sum(impacto) INTO sum_impactos FROM criterio;

> OPEN casos_reales;
> LOOP FETCH casos_reales into cr;
[...]
> OPEN criterios;
> LOOP FETCH criterios into c;
[...]
> SELECT c_r_c.id, valor INTO crc
> FROM caso_real_criterio c_r_c, caso_real c_r,criterio c_
[...]
> SELECT c_e_c.id, valor INTO cec
> FROM caso_escenario_criterio c_e_c, caso_escenario c_e,criterio c_
[...]
> END LOOP;
[...]
> END LOOP;

I did not study your processing in detail, but it looks
like most of that could be expressed as a single
SQL statement that joins the four tables
caso_real, criterio, caso_real_criterio and
caso_escenario_criterio.

Instead you program a nested loop in PL/pgSQL.
That is going to be inefficient.

> I need to decrease the time required function. Please, anyone helpme.

Write as much of your function as you can in SQL.

Yours,
Laurenz Albe