Re: PL/PGSQL arithmetic errors

From: Justin <justin(at)emproshunts(dot)com>
To: jc_mich <juan(dot)michaca(at)paasel(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: PL/PGSQL arithmetic errors
Date: 2009-03-31 03:27:16
Message-ID: 49D18D94.8030701@emproshunts.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<font size="+1"><font face="Arial">Just because a result is unexpected
does not mean its an incorrect result.&nbsp; No postgresql follows the order
of operations as expected.&nbsp; <br>
<br>
Now looking at the 2 For loops the First does not have a where clause
and the Second has&nbsp; a Where not&nbsp; null this could be the cause of the
problem.&nbsp; <br>
<br>
Another note&nbsp; 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 <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.table
GROUP 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 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. </font>
</big></big></pre>
<br>
<font size="+1"><font face="Arial"><br>
<br>
<br>
<br>
<br>
<br>
</font></font><br>
jc_mich wrote:
<blockquote cite="mid:22795583(dot)post(at)talk(dot)nabble(dot)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 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!
</pre>
</blockquote>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 2.7 KB

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Stuart Bishop 2009-03-31 03:35:38 Re: pgstattuple triggered checkpoint failure and database outage?
Previous Message Tom Lane 2009-03-31 03:26:10 Re: string_to_array with empty input