Compilation errors in procedures and functions - PolarDB

When the PolarDB for PostgreSQL (Compatible with Oracle) 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 terminates the compilation process if a parser detects an error. Note that the parsers detect syntax errors in expressions, not semantic errors (that is an expression referencing a non-existent column, table, or function, or an incorrect type).

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 the spl.max_error_count parameter is 10. The maximum value is 1000. Setting the value of spl.max_error_count to 1 instructs the server to stop parsing when it encounters the first error in either SPL or SQL code.

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

SET spl.max_error_count = number_of_errors

number_of_errors specifies the number of SPL errors that may occur before the server stops the compilation process. Example:

SET spl.max_error_count = 6

In the example, codes instruct the server to continue passing the first five SPL errors it encounters. When the server encounters the sixth error it will stop validating, and print six detailed error messages and one error summary.

To save time when you develop new code, or when you import existing code from another source, you can set the spl.max_error_count configuration parameter to a relatively high number of errors.

Note that if you configure the server to continue parsing and ignoring errors in the SPL code in a program body, and the parser encounters an error in a segment of SQL code, there may be errors in any SPL or SQL code that follows the erroneous SQL code. For example, the following content describes a function that results in two errors:

CREATE FUNCTION computeBonus(baseSalary number) RETURN number AS
BEGIN
    bonus := baseSalary * 1.10;
    total := bonus + 100;
    RETURN bonus;
END;

The following result is returned:

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

The following example adds a SELECT statement to the preceding example. The error in the SELECT statement masks 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;

The following result is returned:

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