Functions
Conditional functions
3 Conditional functions in NYXDB: names, aliases, signatures and behaviour.
3 functions. Names resolve case-insensitively; an alias binds the same function as its canonical name. Each entry gives the signatures, the behaviour and an example.
case
Selects the first matching result from ordered SQL branches.
case(WHEN condition1:bool THEN value1:T ... ELSE default:T END) -> common_supertype(T...) nullable
case(x:S WHEN match1:S THEN value1:T ... ELSE default:T END) -> common_supertype(T...) nullable| Kind | scalar |
| Signature class | scalar/unary+generic |
| Returns | common_supertype(T...) nullable |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL_conditions_never_match;selected_result_or_omitted_ELSE_may_be_NULL |
Evaluates searched conditions from left to right, or compares a simple-case input with each candidate using SQL equality. NULL conditions and unknown comparisons do not match; an omitted ELSE produces typed NULL. Result branches need a checked common type or raise TYPE_MISMATCH, and masked execution evaluates only the chosen result.
Example
CASE WHEN 2 > 1 THEN 'ready' ELSE 'waiting' END
-- → 'ready'if
Chooses one of two typed values from a Boolean condition.
if(condition:bool|uint8,then:T,else:T) -> common_supertype(T,T)| Kind | scalar |
| Signature class | scalar/ternary+generic |
| Returns | common_supertype(T,T) |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL_condition_selects_else;selected_branch_NULL_is_preserved |
A true condition selects the second argument; false or NULL selects the third. Only the selected branch is evaluated, preserving a selected NULL and avoiding errors in the unused expression. The branches must have a lossless common result type or binding raises TYPE_MISMATCH.
Example
IF(TRUE, 'ready', 'waiting')
-- → 'ready'multi_if
Returns the value attached to the earliest true condition, with a final default.
multi_if(condition1:bool|uint8,value1:T,...,else:T) -> common_supertype(T...)| Kind | scalar |
| Signature class | scalar/variadic+generic |
| Returns | common_supertype(T...) |
| Aliases | multiIf |
| Streaming | Continuous — evaluated per row with no retained state. |
| Determinism | Deterministic — the same arguments always give the same result. |
| NULL handling | NULL_conditions_are_false;selected_branch_NULL_is_preserved |
Accepts one or more condition/value pairs followed by a default and uses masked left-to-right short circuit. NULL conditions act as false, and a selected NULL value remains NULL. An even argument count or more than fifteen arguments raises INVALID_ARGUMENT; incompatible result branches raise TYPE_MISMATCH.
Example
multi_if(FALSE, 'low', TRUE, 'normal', 'unknown')
-- → 'normal'