Compilation errors in procedures and functions v16

When the EDB Postgres Advanced Server parsers compile a procedure or function, they confirm that both the CREATE statement and the program body (the portion of the program that follows the AS keyword) conform to the grammar rules for SPL and SQL constructs. By default, the server stops compiling if a parser detects an error. The parsers detect syntax errors in expressions, but they don't detect semantic errors. Semantic errors include an expression referencing a nonexistent column, table, or function, or a value of incorrect type.

Setting an error count compilation limit

spl.max_error_count instructs the server to stop parsing if it encounters the specified number of errors in SPL code or when it encounters an error in SQL code. The default value of spl.max_error_count is 10. The maximum value is 1000. Setting spl.max_error_count to a value of 1 instructs the server to stop parsing when it encounters the first error in either SPL or SQL code.

You can use the SET command to specify a value for spl.max_error_count for your current session. The syntax is:

SET spl.max_error_count = <number_of_errors>

Where number_of_errors specifies the number of SPL errors that can occur before the server stops compiling. For example:

SET spl.max_error_count = 6

Example

The example instructs the server to continue past the first five SPL errors it encounters. When the server encounters the sixth error, it stops validating and prints six detailed error messages and one error summary.

To save time when developing new code or when importing code from another source, you might want to set the spl.max_error_count configuration parameter to a relatively high number of errors.

If you instruct the server to continue parsing in spite of errors in the SPL code in a program body, and the parser encounters an error in a segment of SQL code, there can be more errors in any SPL or SQL code that follows the incorrect SQL code. For example, the following function results in two errors:

CREATE FUNCTION computeBonus(baseSalary number) RETURN number AS
BEGIN

    bonus := baseSalary * 1.10;
    total := bonus + 100;

    RETURN bonus;
END;

ERROR:  "bonus" is not a known variable
LINE 4:     bonus := baseSalary * 1.10;
            ^
ERROR:  "total" is not a known variable
LINE 5:     total := bonus + 100;
            ^
ERROR:  compilation of SPL function/procedure "computebonus" failed due to 2 errors

This example adds a SELECT statement to the example. The error in the SELECT statement masks the other errors that follow.

CREATE FUNCTION computeBonus(employeeName number) RETURN number AS
BEGIN
    SELECT salary INTO baseSalary FROM emp
      WHERE ename = employeeName;

    bonus := baseSalary * 1.10;
    total := bonus + 100;

    RETURN bonus;

END;

ERROR:  "basesalary" is not a known variable
LINE 3:     SELECT salary INTO baseSalary FROM emp WHERE ename = emp...