Re: PL/PGSQL arithmetic errors

<font size="+1"><font face="Arial">Just because a result is unexpected does not mean its an incorrect result.  No
postgresqlfollows the order of operations as expected.  <br /><br /> Now looking at the 2 For loops the First does not
havea where clause and the Second has  a Where not  null this could be the cause of the problem.  <br /><br /> 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
byusing a join clause <br /><br /><br /> Example of a Left Join<br /></font></font><br /><pre wrap="">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.tableGROUP BY period Where col1 is not null ) AvgResults 
 
On AvgResults.period = scheme.table.period

WHERE col1
IS NOT NULL
<big><big>
</big></big></pre> <pre wrap=""><big><big><font face="Arial">Assuming i don't have any typos this should give you the
resultsyou 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
singlecolumn and do the update that why.   </font>
 
</big></big></pre><br /><font size="+1"><font face="Arial"><br /><br /><br /><br /><br /><br /></font></font><br />
jc_michwrote: <blockquote cite="mid:22795583.post@talk.nabble.com" type="cite"><pre wrap="">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 LOOPFOR 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.tableSET standata = _standata WHERE id = iterator2.id AND
 
period=iterator2.period;    _standata := 0.0;END LOOP;
END LOOP;

Thanks! </pre></blockquote>