Built-in functions¶

String functions¶

Function	Description
`a ++ b`	Concatenation
`Substr(s, i, l)`	Substring (1-based index)
`Length(s)`	String length
`Upper(s)` / `Lower(s)`	Case conversion
`Split(s, sep)`	Split into an array at each `sep`, taken as text
`Join(list, sep)`	Join array into a string
`Like(s, pattern)`	SQL pattern match: `%` any text, `_` one character, a backslash escapes them (`"50\%"`)
`Format(fmt, ...)`	printf-style formatting
`StartsWith(s, p)` / `EndsWith(s, p)`	Whether `s` starts / ends with `p`
`Strpos(s, p)`	Position of `p` in `s` (1-based), 0 when absent
`Lpad(s, n, p)` / `Rpad(s, n, p)`	`s` padded with `p` on the left / right to `n` characters (cut to `n` when longer)
`Repeat(s, n)`	`s` repeated `n` times
`Replace(s, a, b)`	Every `a` in `s` replaced by `b`
`Trim(s)`	`s` without leading and trailing spaces
`Ltrim(s)` / `Rtrim(s)`	`s` without leading / trailing spaces
`Reverse(s)`	The characters of `s` in reverse order
`RegexpContains(s, r)`	Whether the regular expression `r` matches in `s`
`RegexpExtract(s, r)`	The first match of `r` in `s`, null when none
`RegexpReplace(s, r, b)`	Every match of `r` in `s` replaced by `b`

Array functions¶

Function	Description
`Size(a)`	Number of elements
`Element(a, i)`	Element access (0-based index); null below 0 or past the end
`ArrayConcat(a, b)`	Concatenate arrays
`Range(n)`	Array `[0, 1, ..., n-1]`, empty for 0 or less

Size of an empty list is 0, of a null list null. Split("", ",") is one empty part, [""], and Join of an empty list is the empty text.

Math functions¶

Abs, Floor, Ceil, Round, Sqrt, Exp, Log (natural), Sin, Cos, Pow(x, y) (x to the power y, a double, as x ^ y).

Greatest(x, y, ...) and Least(x, y, ...) are the greatest and least of their arguments, and null when one is null, as any arithmetic of a null is: Greatest(x, Coalesce(y, 0)) skips a null y.

Round(x) rounds a half away from zero (Round(2.5) is 3, Round(-2.5) is -3). Round(x, digits) rounds the number as its text shows it, at 15 significant digits, half away from zero, as a spreadsheet does: Round(1.005, 2) is 1.01 and Round(2.675, 2) is 2.68 on every engine, though the doubles nearest 1.005 and 2.675 are just below them; a negative number of digits rounds to tens, hundreds, ... (Round(1234, -2) is 1200).

Type casting¶

Integer literals are 32-bit on DuckDB, PostgreSQL, Trino, Presto and Databricks, where arithmetic past 2^31 on them fails (or, on Spark without ANSI mode, wraps around): make such arithmetic 64-bit with ToInt64, as in ToInt64(1024) * 1024 * 1024 * 1024.

Function	Description
`ToInt64(x)`	Cast to a 64-bit integer (a fraction is rounded)
`ToFloat64(x)`	Cast to float
`ToString(x)`	Cast to string. A number's text is the same on every engine: a whole number has no decimal point (`5`, every digit below 10^18); any other is the number as written (the shortest text that reads back as the same double) rounded half away from zero to 15 significant digits but at most 15 decimals, in plain decimal, without trailing zeros (`ToString(0.1 + 0.2)` is `"0.3"`, `ToString(1 / 3)` is `"0.333333333333333"`, `ToString(342547.0843250365)` is `"342547.084325037"`, `ToString(1e20)` is `"100000000000000000000"`)

Other¶

Function	Description
`IsNull(x)`	Null test as an expression
`Coalesce(x, y, ...)`	First non-null argument
`Ifnull(x, y)`	`x`, or `y` when `x` is null
`If(c, a, b)`	`a` when `c` holds, else `b`
`Trunc(x)`	`x` without its fraction (toward zero)
`Div(a, b)`	The whole quotient of `a` by `b` (toward zero)
`Constraint(expr)`	Filter rows by a boolean expression

SqlExpr is reserved for the built-in library

SqlExpr(s, r) injects raw, unparsed, non-portable SQL into the compiled query, bypassing every verification and portability guarantee. It is used internally by the dialect library (e.g. ArgMin/ArgMax), but the verifier rejects it in user programs. Express the logic in Synalog instead. For date/time math, use the Substr → ToInt64 → ToString pipeline.

Indexing conventions

Substr is 1-based (SQL convention); Element is 0-based (array convention).

The functions listed on this page run, and give the same result, on every engine. A call of a function that the program does not define and that is not a built-in is refused by the verifier (Undefined function 'Substrr' ... did you mean 'Substr'?): it would otherwise read a table of that name.

User-defined functions¶

Define pure functions with =:

Square(x) = x * x;
FullName(first, last) = first ++ " " ++ last;

A function may not take the name of a built-in function (Upper, Size, Pow, ...): it would change what the name means in the whole program, and the verifier refuses it. A relation may (Rank(x:)): it is never called as a function.

Greeting(message:) :- Users(first_name:, last_name:),
  message == "Hello, " ++ FullName(first_name, last_name) ++ "!";

A function can have a body: its value is the one the body binds, as a rule's head value is. A body that reads a table makes a lookup, which joins: a row with no match gets no value, so the rule using it gives no row for it (write a combine to get a null instead):

Inc(x) = n :- n == x + 1;
RateOf(currency) = rate :- ExchangeRate(currency:, rate:);
Converted(order_id:, eur: amount * RateOf(currency)) :- Orders(order_id:, amount:, currency:);

A function can return a record, read with a dot: Point(x) = {x:, y: x * x}; then Point(3).y is 9.

A function is a value: compare it to filter. Written as a condition, IsEven(x) would hold for every x (a function has a row for each argument, true or false), so the verifier refuses it:

IsEven(x) = (x % 2 == 0);
Even(x:) :- Numbers(x:), IsEven(x) == true;

Complete example¶

String, math and casting functions, plus two user-defined functions:

# run: ProductLabel, Rounded, Casts
@Engine("duckdb");

Products(name: "espresso machine", price: 249.99);
Products(name: "grinder", price: 79.5);

# User-defined functions
Initial(s) = Upper(Substr(s, 1, 1));
FullLabel(name, price) = Upper(name) ++ " ($" ++ ToString(price) ++ ")";

# String functions and concatenation.
@OrderBy(ProductLabel, "name");
ProductLabel(name:, label:, initial:, name_length:) :-
  Products(name:, price:),
  label == FullLabel(name, price),
  initial == Initial(name),
  name_length == Length(name);

# Math functions.
@OrderBy(Rounded, "name");
Rounded(name:, rounded:, floor:, sqrt:) :-
  Products(name:, price:),
  rounded == Round(price),
  floor == Floor(price),
  sqrt == Round(Sqrt(price));

# Type casting.
@OrderBy(Casts, "name");
Casts(name:, cents:, as_text:) :-
  Products(name:, price:),
  cents == ToInt64(price * 100),
  as_text == ToString(price);

Generated SQL and execution results

$ synalog.check('functions.l')
No errors found.

$ synalog.compile('functions.l', 'ProductLabel')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_1_Products AS (SELECT * FROM (

    SELECT
      'espresso machine' AS name,
      249.99E0 AS price
   UNION ALL

    SELECT
      'grinder' AS name,
      79.5E0 AS price

) AS UNUSED_TABLE_NAME  )
SELECT
  Products.name AS name,
  ((((((UPPER(Products.name)) || (' ($'))) || (list_transform([Products.price], synalog_v -> (CASE WHEN synalog_v IS NULL THEN NULL WHEN ABS(synalog_v) < 0.0000000000000005 THEN '0' WHEN synalog_v = FLOOR(synalog_v) AND ABS(synalog_v) < 1e18 THEN CAST(CAST(synalog_v AS BIGINT) AS VARCHAR) WHEN ABS(synalog_v) >= 1e38 THEN CAST(synalog_v AS VARCHAR) WHEN ABS(synalog_v) < 1 THEN TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM CAST(CAST(ROUND(CAST(CAST(synalog_v AS VARCHAR) AS DECIMAL(38,35)), 15) AS DECIMAL(38,35)) AS VARCHAR))) WHEN ABS(synalog_v) < 1e18 THEN list_transform([CAST(CAST(synalog_v AS VARCHAR) AS DECIMAL(38,20))], synalog_t -> TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM CAST(CASE LENGTH(CAST(CAST(FLOOR(ABS(synalog_t)) AS BIGINT) AS VARCHAR)) WHEN 1 THEN CAST(ROUND(synalog_t, 14) AS DECIMAL(38,20)) WHEN 2 THEN CAST(ROUND(synalog_t, 13) AS DECIMAL(38,20)) WHEN 3 THEN CAST(ROUND(synalog_t, 12) AS DECIMAL(38,20)) WHEN 4 THEN CAST(ROUND(synalog_t, 11) AS DECIMAL(38,20)) WHEN 5 THEN CAST(ROUND(synalog_t, 10) AS DECIMAL(38,20)) WHEN 6 THEN CAST(ROUND(synalog_t, 9) AS DECIMAL(38,20)) WHEN 7 THEN CAST(ROUND(synalog_t, 8) AS DECIMAL(38,20)) WHEN 8 THEN CAST(ROUND(synalog_t, 7) AS DECIMAL(38,20)) WHEN 9 THEN CAST(ROUND(synalog_t, 6) AS DECIMAL(38,20)) WHEN 10 THEN CAST(ROUND(synalog_t, 5) AS DECIMAL(38,20)) WHEN 11 THEN CAST(ROUND(synalog_t, 4) AS DECIMAL(38,20)) WHEN 12 THEN CAST(ROUND(synalog_t, 3) AS DECIMAL(38,20)) WHEN 13 THEN CAST(ROUND(synalog_t, 2) AS DECIMAL(38,20)) WHEN 14 THEN CAST(ROUND(synalog_t, 1) AS DECIMAL(38,20)) WHEN 15 THEN CAST(ROUND(synalog_t, 0) AS DECIMAL(38,20)) WHEN 16 THEN CAST(ROUND(synalog_t, -1) AS DECIMAL(38,20)) WHEN 17 THEN CAST(ROUND(synalog_t, -2) AS DECIMAL(38,20)) WHEN 18 THEN CAST(ROUND(synalog_t, -3) AS DECIMAL(38,20)) END AS VARCHAR))))[1] ELSE list_transform([CAST(CAST(synalog_v AS VARCHAR) AS DECIMAL(38,0))], synalog_t -> CAST(CASE LENGTH(CAST(ABS(synalog_t) AS VARCHAR)) WHEN 19 THEN CAST(ROUND(synalog_t, -4) AS DECIMAL(38,0)) WHEN 20 THEN CAST(ROUND(synalog_t, -5) AS DECIMAL(38,0)) WHEN 21 THEN CAST(ROUND(synalog_t, -6) AS DECIMAL(38,0)) WHEN 22 THEN CAST(ROUND(synalog_t, -7) AS DECIMAL(38,0)) WHEN 23 THEN CAST(ROUND(synalog_t, -8) AS DECIMAL(38,0)) WHEN 24 THEN CAST(ROUND(synalog_t, -9) AS DECIMAL(38,0)) WHEN 25 THEN CAST(ROUND(synalog_t, -10) AS DECIMAL(38,0)) WHEN 26 THEN CAST(ROUND(synalog_t, -11) AS DECIMAL(38,0)) WHEN 27 THEN CAST(ROUND(synalog_t, -12) AS DECIMAL(38,0)) WHEN 28 THEN CAST(ROUND(synalog_t, -13) AS DECIMAL(38,0)) WHEN 29 THEN CAST(ROUND(synalog_t, -14) AS DECIMAL(38,0)) WHEN 30 THEN CAST(ROUND(synalog_t, -15) AS DECIMAL(38,0)) WHEN 31 THEN CAST(ROUND(synalog_t, -16) AS DECIMAL(38,0)) WHEN 32 THEN CAST(ROUND(synalog_t, -17) AS DECIMAL(38,0)) WHEN 33 THEN CAST(ROUND(synalog_t, -18) AS DECIMAL(38,0)) WHEN 34 THEN CAST(ROUND(synalog_t, -19) AS DECIMAL(38,0)) WHEN 35 THEN CAST(ROUND(synalog_t, -20) AS DECIMAL(38,0)) WHEN 36 THEN CAST(ROUND(synalog_t, -21) AS DECIMAL(38,0)) WHEN 37 THEN CAST(ROUND(synalog_t, -22) AS DECIMAL(38,0)) WHEN 38 THEN CAST(ROUND(synalog_t, -23) AS DECIMAL(38,0)) END AS VARCHAR))[1] END))[1]))) || (')')) AS label,
  UPPER(SUBSTR(Products.name, 1, 1)) AS initial,
  LENGTH(Products.name) AS name_length
FROM
  t_1_Products AS Products ORDER BY name;

-- Executed on DuckDB:
| name             | label                      | initial | name_length |
|------------------|----------------------------|---------|-------------|
| espresso machine | ESPRESSO MACHINE ($249.99) | E       | 16          |
| grinder          | GRINDER ($79.5)            | G       | 7           |
(2 rows)

$ synalog.compile('functions.l', 'Rounded')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_Products AS (SELECT * FROM (

    SELECT
      'espresso machine' AS name,
      249.99E0 AS price
   UNION ALL

    SELECT
      'grinder' AS name,
      79.5E0 AS price

) AS UNUSED_TABLE_NAME  )
SELECT
  Products.name AS name,
  ROUND(Products.price) AS rounded,
  FLOOR(Products.price) AS floor,
  ROUND(SQRT(Products.price)) AS sqrt
FROM
  t_0_Products AS Products ORDER BY name;

-- Executed on DuckDB:
| name             | rounded | floor | sqrt |
|------------------|---------|-------|------|
| espresso machine | 250.0   | 249.0 | 16.0 |
| grinder          | 80.0    | 79.0  | 9.0  |
(2 rows)

$ synalog.compile('functions.l', 'Casts')
-- Initializing DuckDB environment.
create schema if not exists logica_home;
-- Empty record, has to have a field by DuckDB syntax.
drop type if exists logicarecord893574736 cascade; create type logicarecord893574736 as struct(nirvana numeric);
create sequence if not exists eternal_logical_sequence;

WITH t_0_Products AS (SELECT * FROM (

    SELECT
      'espresso machine' AS name,
      249.99E0 AS price
   UNION ALL

    SELECT
      'grinder' AS name,
      79.5E0 AS price

) AS UNUSED_TABLE_NAME  )
SELECT
  Products.name AS name,
  CAST(ROUND(((Products.price) * (100))) AS BIGINT) AS cents,
  list_transform([Products.price], synalog_v -> (CASE WHEN synalog_v IS NULL THEN NULL WHEN ABS(synalog_v) < 0.0000000000000005 THEN '0' WHEN synalog_v = FLOOR(synalog_v) AND ABS(synalog_v) < 1e18 THEN CAST(CAST(synalog_v AS BIGINT) AS VARCHAR) WHEN ABS(synalog_v) >= 1e38 THEN CAST(synalog_v AS VARCHAR) WHEN ABS(synalog_v) < 1 THEN TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM CAST(CAST(ROUND(CAST(CAST(synalog_v AS VARCHAR) AS DECIMAL(38,35)), 15) AS DECIMAL(38,35)) AS VARCHAR))) WHEN ABS(synalog_v) < 1e18 THEN list_transform([CAST(CAST(synalog_v AS VARCHAR) AS DECIMAL(38,20))], synalog_t -> TRIM(TRAILING '.' FROM TRIM(TRAILING '0' FROM CAST(CASE LENGTH(CAST(CAST(FLOOR(ABS(synalog_t)) AS BIGINT) AS VARCHAR)) WHEN 1 THEN CAST(ROUND(synalog_t, 14) AS DECIMAL(38,20)) WHEN 2 THEN CAST(ROUND(synalog_t, 13) AS DECIMAL(38,20)) WHEN 3 THEN CAST(ROUND(synalog_t, 12) AS DECIMAL(38,20)) WHEN 4 THEN CAST(ROUND(synalog_t, 11) AS DECIMAL(38,20)) WHEN 5 THEN CAST(ROUND(synalog_t, 10) AS DECIMAL(38,20)) WHEN 6 THEN CAST(ROUND(synalog_t, 9) AS DECIMAL(38,20)) WHEN 7 THEN CAST(ROUND(synalog_t, 8) AS DECIMAL(38,20)) WHEN 8 THEN CAST(ROUND(synalog_t, 7) AS DECIMAL(38,20)) WHEN 9 THEN CAST(ROUND(synalog_t, 6) AS DECIMAL(38,20)) WHEN 10 THEN CAST(ROUND(synalog_t, 5) AS DECIMAL(38,20)) WHEN 11 THEN CAST(ROUND(synalog_t, 4) AS DECIMAL(38,20)) WHEN 12 THEN CAST(ROUND(synalog_t, 3) AS DECIMAL(38,20)) WHEN 13 THEN CAST(ROUND(synalog_t, 2) AS DECIMAL(38,20)) WHEN 14 THEN CAST(ROUND(synalog_t, 1) AS DECIMAL(38,20)) WHEN 15 THEN CAST(ROUND(synalog_t, 0) AS DECIMAL(38,20)) WHEN 16 THEN CAST(ROUND(synalog_t, -1) AS DECIMAL(38,20)) WHEN 17 THEN CAST(ROUND(synalog_t, -2) AS DECIMAL(38,20)) WHEN 18 THEN CAST(ROUND(synalog_t, -3) AS DECIMAL(38,20)) END AS VARCHAR))))[1] ELSE list_transform([CAST(CAST(synalog_v AS VARCHAR) AS DECIMAL(38,0))], synalog_t -> CAST(CASE LENGTH(CAST(ABS(synalog_t) AS VARCHAR)) WHEN 19 THEN CAST(ROUND(synalog_t, -4) AS DECIMAL(38,0)) WHEN 20 THEN CAST(ROUND(synalog_t, -5) AS DECIMAL(38,0)) WHEN 21 THEN CAST(ROUND(synalog_t, -6) AS DECIMAL(38,0)) WHEN 22 THEN CAST(ROUND(synalog_t, -7) AS DECIMAL(38,0)) WHEN 23 THEN CAST(ROUND(synalog_t, -8) AS DECIMAL(38,0)) WHEN 24 THEN CAST(ROUND(synalog_t, -9) AS DECIMAL(38,0)) WHEN 25 THEN CAST(ROUND(synalog_t, -10) AS DECIMAL(38,0)) WHEN 26 THEN CAST(ROUND(synalog_t, -11) AS DECIMAL(38,0)) WHEN 27 THEN CAST(ROUND(synalog_t, -12) AS DECIMAL(38,0)) WHEN 28 THEN CAST(ROUND(synalog_t, -13) AS DECIMAL(38,0)) WHEN 29 THEN CAST(ROUND(synalog_t, -14) AS DECIMAL(38,0)) WHEN 30 THEN CAST(ROUND(synalog_t, -15) AS DECIMAL(38,0)) WHEN 31 THEN CAST(ROUND(synalog_t, -16) AS DECIMAL(38,0)) WHEN 32 THEN CAST(ROUND(synalog_t, -17) AS DECIMAL(38,0)) WHEN 33 THEN CAST(ROUND(synalog_t, -18) AS DECIMAL(38,0)) WHEN 34 THEN CAST(ROUND(synalog_t, -19) AS DECIMAL(38,0)) WHEN 35 THEN CAST(ROUND(synalog_t, -20) AS DECIMAL(38,0)) WHEN 36 THEN CAST(ROUND(synalog_t, -21) AS DECIMAL(38,0)) WHEN 37 THEN CAST(ROUND(synalog_t, -22) AS DECIMAL(38,0)) WHEN 38 THEN CAST(ROUND(synalog_t, -23) AS DECIMAL(38,0)) END AS VARCHAR))[1] END))[1] AS as_text
FROM
  t_0_Products AS Products ORDER BY name;

-- Executed on DuckDB:
| name             | cents | as_text |
|------------------|-------|---------|
| espresso machine | 24999 | 249.99  |
| grinder          | 7950  | 79.5    |
(2 rows)