PL/PGSQL arithmetic errors

Поиск
Список
Период
Сортировка
Искать

PL/PGSQL arithmetic errors

От:
jc_mich <juan.michaca@paasel.com>
Дата:

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!
-- 
View this message in context: http://www.nabble.com/PL-PGSQL-arithmetic-errors-tp22795583p22795583.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: PL/PGSQL arithmetic errors

От:
Justin <justin@emproshunts.com>
Дата:
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.tableGROUP 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 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.table SET standata = _standata WHERE id = iterator2.id AND
period=iterator2.period;	_standata := 0.0;END LOOP;
END LOOP;

Thanks!
FAQ