This release adds fifteen new SPL grammar elements spanning control flow, aggregate functions, query modifiers, DDL, TCL, and string operations. The additions close gaps that previously required dropping to native SQL: ordered-set percentile aggregates, row-level locking hints, conflict-resolution set operators, and structured exception propagation are now first-class XML constructs. An enterprise payroll engine showcase, added alongside the grammar work, exercises the new control flow and aggregate surface against a realistic payroll calculation workload and serves as the integration test suite for the expanded language.
Control flow and exception handling
<raise-sqlstate>. Raises a structured SQL exception with a declared SQLSTATE code from within an SPL procedure, enabling callers to catch and handle specific error conditions rather than relying on untyped error propagation.<perform>. Evaluates an expression as a statement, for calling procedures or functions whose return values are intentionally discarded without requiring an assignment target.<loop>. Provides an unconditional infinite loop construct, exited via existing break or return statements, for polling, retry, and event-wait patterns whose exit condition is determined internally.
Aggregate functions
<percentile_cont>and<percentile_disc>. Ordered-set aggregates for continuous and discrete percentile calculation, compiled to native aggregate syntax for each target database engine.<filter where='...'>. Applies a conditional predicate inside an aggregate call — equivalent to SQL'sFILTER (WHERE ...)clause — without requiring a subquery or CASE expression wrapper.<array_agg>. Aggregates a column's values into an ordered array within a single query pass.
Query modifiers and set operators
- INTERSECT, EXCEPT, MINUS. All three set operators are available in
<union>elements; the compiler selects MINUS or EXCEPT based on the target database dialect. - FOR UPDATE and SKIP LOCKED. Row-level locking hints for queue-pattern workloads:
for-updateacquires write locks on selected rows;skip-lockedpasses over rows already held by a concurrent transaction. - DISTINCT ON. Selects one row per distinct value of a given expression, emitted as
SELECT DISTINCT ON (...)on compatible engines. - NULLS FIRST / NULLS LAST. Explicit null placement in ORDER BY clauses, normalising the default null-ordering behaviour that varies across database engines.
<order-by>and<group-by>aliases. Both elements are now accepted as readable alternatives to<order>and<group>in SELECT grammar, with identical semantics.
DDL, TCL, and string functions
<refresh-view>. Refreshes a materialized view as a first-class DDL statement, replacing ad-hoc procedure calls for scheduled view maintenance.<set-transaction-isolation>. Sets the isolation level for the current transaction — READ COMMITTED, REPEATABLE READ, or SERIALIZABLE — as a portable TCL element with cross-database compilation.<truncate>. Truncates a table as a DDL element, mapped toTRUNCATE TABLEon engines that support it.<initcap>and<nvl2>. String utility functions:<initcap>converts a string to proper case;<nvl2>returns one of two expressions depending on whether its first argument is null, with cross-database translation where the native function is absent.
The payroll engine showcase covers period aggregation, multi-bracket tax calculation, and exception code propagation — workloads that exercise the new aggregate filters, ordered-set percentiles, and structured exception handling together. The showcase runs against each supported database backend as part of the standard test suite.