Albe Laurenz <laurenz.albe@xxxxxxxxxx> wrote: > Karel Riverón wrote: >> I have a PL/pgSQL function that it takes 4 seconds to execute. >> 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 +1 On a quick look, it seems like someting along these lines might be what you want (although I had to guess a little at schema, since you didn't show it): SELECT cr.* FROM (SELECT sum(impactos) AS sum_impactos FROM criterio) si CROSS JOIN criterio c JOIN caso_real_criterio crc ON (crc.criterio_id = c.id) JOIN caso_real cr ON (cr.id = crc.caso_real_id) JOIN caso_escenario_criterio cec ON (cec.criterio_id = c.id) JOIN caso_escenario ce ON (ce.id = cec.caso_escenario_id) WHERE ce.id = escenario_id GROUP BY cr.id HAVING sum(c.impacto * (1 - abs(crc.valor - cec.valor) / 5)) / si.sum_impactos >= 0.75 ; You might need to adjust the GROUP BY clause if you're not running a recent major release. If you want to keep it as a function, you can throw out the DECLARE section and everything between the outermost BEGIN and END, and replace it with RETURN QUERY and the above query, or turn it into a SQL function to avoid the overhead of materializing the entire result set. If you get some form of that to run, please post back with a comparison of run times. -Kevin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
