Literals and Math Operations in Expressions If your stored procedures perform math operations in expressions, carefully consider the supported ranges for literals and the possible implicit typecasts that the Netezza system may perform. These ranges and behaviors could cause unexpected results in complex math expressions.

For example, all integer literals are 32-bit (signed) numbers by default, which supports a range of values from -2147483648 to 2147483647. If you perform math operations on a literal, use caution to check for overflows. For example, the following stored procedure defines a numeric value n, assigns it the largest supported value (2147483647) and then adds 1 to the value:

CREATE OR REPLACE PROCEDURE num() RETURNS BOOL LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE
n NUMERIC;
BEGIN
n := 2147483647;
RAISE NOTICE 'n is %', n;
n := 2147483647 + 1;
RAISE NOTICE 'n is %', n;
END;
END_PROC;

If you create and run this stored procedure, the output is as follows:

NOTICE: n is 2147483647
NOTICE: n is -2147483648

The output demonstrates that when a value “overflows” its maximum range value, the value wraps to its minimum value and begins again. To avoid this counter-wrap behavior, you must either use a cast operation to cast n to a higher precision type, or assign values to intermediate values. For example, the following stored procedure creates the counter-wrap issue for the numeric value n, but then

CREATE OR REPLACE PROCEDURE SP_NEW_PROCEDURE() RETURNS BOOLEAN LANGUAGE NZPLSQL AS 
BEGIN_PROC 
DECLARE n NUMERIC; BEGIN
	
	n := 2147483647;
	RAISE NOTICE 'n is %', n;
	n := 2147483647 + 1;
	RAISE NOTICE 'n is %', n;
END;
END_PROC;

Результат:
ERROR [01000] NOTICE:  n is 2147483647

ERROR [01000] NOTICE:  Error occurred while executing PL/pgSQL function SP_NEW_PROCEDURE

ERROR [01000] NOTICE:  line 7 at assignment

ERROR [HY000] ERROR:  overflow in 32 bit arithmetic