Expressions

All expressions used in NZPLSQL statements are processed by using the backend executor.

Expressions that appear to contain constants could require runtime evaluation (for example, by using 'now()' for the timestamp type) so it is impossible for the NZPLSQL parser to identify real constant values other than the NULL keyword. All expressions are evaluated internally by running a query such as the following:

SELECT expression

In the expression, occurrences of variable identifiers are substituted by parameters and the actual values from the variables are passed to the executor in the parameter array. All expressions used in an NZPLSQL procedure are compiled and cached the first time they are encountered. Everything in NZPLSQL is cached, except for variable values and SQL plans. If there is a compile error (syntax error), the expression is not cached. The cached copy is preserved until one of the following happens:

The procedure body is modified.
The procedure is dropped.
The database session ends.

The type checking done by the Netezza Performance Server SQL main parser has some side effects for the interpretation of constant values. For example, the following two examples are different in how constant values are interpreted. The first example follows:

DECLARE
    logtxt ALIAS FOR $1;
BEGIN
    INSERT INTO logtable VALUES (logtxt, 'now()');
    RETURN 'now()';
END;

The second example follows:

DECLARE
    logtxt ALIAS FOR $1;
    curtime timestamp;
BEGIN
    curtime := 'now()';
    INSERT INTO logtable VALUES (logtxt, curtime);
    RETURN curtime;
END

In the first example, when the Netezza Performance Server SQL main parser prepares the plan for the INSERT, it interprets now() as a timestamp because the target field of logtable is of that type. It interprets both instances of now() each time it runs.

In the second example, the Netezza Performance Server SQL main parser does not know what type now() should become and therefore it returns a data type of text that contains the string now(). During the assignment to the local variable curtime, the NZPLSQL interpreter casts this string to the timestamp type by calling the text_out() and timestamp_in() functions for the conversion.

If record fields are used in expressions or statements, the data types of fields should not change between calls to the same expression or statement.