Just because a result is unexpected
does not mean its an incorrect result. No postgresql follows the order
of operations as expected. Now looking at the 2 For loops the First does not have a where clause and the Second has a Where not null this could be the cause of the problem. Another note you don't need to do this in nested For loops it can be done in a single select statement using nested queries or by using a join clause Example of a Left Join Select period, id, (col2-avgResults.col2)/AvgResults.dev_col2, (col1 - AvgResults.col1) / AvgResults.dev_col1 FROM scheme.table, Left Join (SELECT period, AVG(col1) AS avg_col1, STDDEV(col1) AS dev_col1, AVG(col2) AS avg_col2, STDDEV(col2) AS dev_col2 FROM scheme.table GROUP BY period Where col1 is not null ) AvgResults On AvgResults.period = scheme.table.period WHERE col1 IS NOT NULL Assuming i don't have any typos this should give you the results you are looking for and be faster. You can throw in a Case statement in the select testing for grav to limit the result down and speed things up to a single column and do the update that why. jc_mich wrote: Hi all! I'm developing an algorithm with PL/PGSQL using statistical operations from a table. I have several differences between expected results and the results generated by the function. I want to know if there are differences in arithmetic operation sintax or if there's any special arithmetical operators precedence between pl/pgsql and other languages. My code looks like this: FOR iterator1 IN SELECT period, AVG(col1) AS avg_col1, STDDEV(col1) AS dev_col1, AVG(col2) AS avg_col2, STDDEV(col2) AS dev_col2 FROM scheme.table GROUP BY period ORDER BY period LOOP FOR iterator2 IN SELECT period, id, col2, col1 FROM scheme.table WHERE col1 IS NOT NULL LOOP IF grav = 0 THEN _standata := (iterator2.col1 - iterator1.avg_col1) / iterator1.dev_col1; ELSE _standata := (iterator2.col2 - iterator1.avg_col2) / iterator1.dev_col2; END IF; UPDATE scheme.table SET standata = _standata WHERE id = iterator2.id AND period=iterator2.period; _standata := 0.0; END LOOP; END LOOP; Thanks! |
- [GENERAL] PL/PGSQL arithmetic errors jc_mich
- Re: [GENERAL] PL/PGSQL arithmetic errors Justin