# 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 Performance Server system might perform. These ranges and behaviors can cause unexpected results or overflow errors in complex math expressions.

```
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 procedure fails and returns an integer overflow error:

```
NOTICE: n is 2147483647
NOTICE: Error occurred while executing PL/pgSQL function NUM
NOTICE: line 6 at assignment
ERROR: overflow in 32 bit arithmetic
```

`n`

to a higher precision type, or assign values
to intermediate values. For example, the following stored procedure
creates the overflow issue for the numeric value n but uses casts
to numerics or bigints to increase the range of valid values:```
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::numeric + 1;
RAISE NOTICE 'n is %', n;
n := 2147483647::bigint + 1;
RAISE NOTICE 'n is %', n;
END;
END_PROC;
```

```
NOTICE: n is 2147483647
NOTICE: n is 2147483648
NOTICE: n is 2147483648
```

As this example shows, explicit casts during arithmetic evaluations and careful use of literals, constants, and types can help to increase the accuracy of the expressions used in your stored procedures.

If you use floating point numbers in expressions, Netezza Performance Server attempts to cast it into a numeric if possible, with a specific precision and scale that it calculates with internally defined casting rules.

Because arithmetic operations in a stored procedure are evaluated by invoking the backend executor, they are processed as SELECT statements. To more clearly see the calculated shape of the expression result, it can be helpful to use it to create a table, which can then be described by using the \d command. Printing the results might not provide enough insight to the resulting data types.

```
DEV.SCH1(ADMIN)=> create table test as select (1 + 0.08/365) interest;
INSERT 0 1
DEV.SCH1(ADMIN)=> \d test
Table "TEST"
Attribute | Type | Modifier | Default Value
----------+--------------+----------+---------------
INTEREST | NUMERIC(8,6) | |
Distributed on hash: "INTEREST"
```

```
DEV.SCH1(ADMIN)=> select * from TEST;
INTEREST
----------
1.000219
(1 row)
```

```
DEV.SCH1(ADMIN)=> create table test2 as select (4 + 1/5) loanrt;
INSERT 0 1
DEV.SCH1(ADMIN)=> \d test2
Table "TEST2"
Attribute | Type | Modifier | Default Value
----------+----------+----------+---------------
LOANRT | INTEGER | |
Distributed on hash: "LOANRT"
```

```
DEV.SCH1(ADMIN)=> select * from TEST2;
LOANRT
--------
4
(1 row)
```

```
DEV.SCH1(ADMIN)=> create table test3 as select (4 + .5) loanrt;
INSERT 0 1
DEV.SCH1(ADMIN)=> \d test3
Table "TEST3"
Attribute | Type | Modifier | Default Value
----------+---------------+----------+---------------
LOANRT | NUMERIC(3,1) | |
Distributed on hash: "LOANRT"
DEV.SCH1(ADMIN)=> select * from TEST3;
LOANRT
--------
4.5
(1 row)
```

In this example, the `.5`

value is interpreted as
0.5, and thus cast to numeric(3,1).

`sqrt()`

takes
and returns a double precision value. The following example uses the
function to define a table column:```
DEV.SCH1(ADMIN)=> create table test4 as select (sqrt(42));
INSERT 0 1
DEV.SCH1(ADMIN)=> \d test4
Table "TEST4"
Attribute | Type | Modifier | Default Value
----------+------------------+----------+---------------
SQRT | DOUBLE PRECISION | |
Distributed on hash: "SQRT"
DEV.SCH1(ADMIN)=> select * from TEST4;
SQRT
-----------------
6.4807406984079
(1 row)
```

In the test4 example, the `sqrt()`

function causes Netezza Performance Server to
cast the input integer value to a double and to return a double.

```
CREATE OR REPLACE PROCEDURE sp_expressions_numeric02() RETURNS NUMERIC
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
thisnum9_2 NUMERIC(9,2);
million NUMERIC(9,2) := 1000000.00;
thisnum18 NUMERIC(18);
litespeed NUMERIC := 186282;
thisnum38 NUMERIC(38);
BEGIN
/* The following expression causes implicit casts in the math
evaluation, reducing the precision and scale of the result. */
thisnum9_2 := million * (1 + 0.08/365)^(365 * 20);
RAISE NOTICE 'thisnum9_2 == %', thisnum9_2;
/* The following expression uses an explicit cast to increase
the precision and scale of the intermediate evaluation. */
thisnum9_2 := million * (1 + 0.08::numeric(20,15)/365)^(365 * 20);
RAISE NOTICE 'thisnum9_2 == %', thisnum9_2;
/* The following expression uses the numeric literal litespeed to
convert the speed of light from miles per sec to miles per
year. */
thisnum18 := litespeed * 60 * 60 * 24 * 365.25;
RAISE NOTICE 'thisnum18 == %', thisnum18;
/* The following expression uses the integer 186282 to convert
lightspeed from miles per sec to miles per year. In the
right-side evaluation, however, the values overflowed the
upper limit of an int several times during evaluation,
which causes an overflow error. */
thisnum38 := 186282 * 60 * 60 * 24 * 365.25;
RAISE NOTICE 'thisnum38 == %', thisnum38;
END;
END_PROC;
```

```
NOTICE: thisnum9_2 == 4945731.93
NOTICE: thisnum9_2 == 4952164.15
NOTICE: thisnum18 == 5878612843200
NOTICE: Error occurred while executing PL/pgSQL function SP_EXPRESSIONS_NUMERIC02
NOTICE: line 16 at assignment
ERROR: overflow in 32 bit arithmetic
```