Functions

Examples on this page use this small demo schema:

CREATE TABLE users  (id BIGINT PRIMARY KEY, username TEXT, email TEXT, status TEXT, created_at TIMESTAMP);
CREATE TABLE sales  (id BIGINT PRIMARY KEY, date DATE, amount DOUBLE PRECISION, region TEXT, category TEXT);
CREATE TABLE events (id BIGINT PRIMARY KEY, name TEXT, payload JSONB, occurred_at TIMESTAMP);

ScramDB provides 80+ scalar functions, plus aggregate and window functions, and JSON, array, and system helpers.

Scalar Functions

String Functions

Function	Syntax	Description
`LENGTH`	`LENGTH(str)`	Number of characters
`UPPER`	`UPPER(str)`	Convert to uppercase
`LOWER`	`LOWER(str)`	Convert to lowercase
`SUBSTRING`	`SUBSTRING(str FROM start FOR len)`	Extract substring
`TRIM`	`TRIM(str)`	Remove leading/trailing whitespace
`LTRIM`	`LTRIM(str)`	Remove leading whitespace
`RTRIM`	`RTRIM(str)`	Remove trailing whitespace
`BTRIM`	`BTRIM(str, chars)`	Remove specified characters from both ends
`CONCAT`	`CONCAT(str1, str2, ...)`	Concatenate strings
`CONCAT_WS`	`CONCAT_WS(sep, str1, str2, ...)`	Concatenate with separator
`REPLACE`	`REPLACE(str, from, to)`	Replace all occurrences
`POSITION`	`POSITION(substr IN str)`	Find substring position (1-based)
`LEFT`	`LEFT(str, n)`	First n characters
`RIGHT`	`RIGHT(str, n)`	Last n characters
`LPAD`	`LPAD(str, len, fill)`	Pad left to length
`RPAD`	`RPAD(str, len, fill)`	Pad right to length
`REPEAT`	`REPEAT(str, n)`	Repeat string n times
`REVERSE`	`REVERSE(str)`	Reverse string
`INITCAP`	`INITCAP(str)`	Capitalize first letter of each word
`SPLIT_PART`	`SPLIT_PART(str, delim, n)`	Extract nth field
`STARTS_WITH`	`STARTS_WITH(str, prefix)`	Check prefix
`ENDS_WITH`	`ENDS_WITH(str, suffix)`	Check suffix
`ASCII`	`ASCII(str)`	ASCII code of first character
`CHR`	`CHR(code)`	Character from ASCII code
`MD5`	`MD5(str)`	MD5 hash as hex string

Math Functions

Function	Syntax	Description
`ABS`	`ABS(x)`	Absolute value
`CEIL` / `CEILING`	`CEIL(x)`	Round up to nearest integer
`FLOOR`	`FLOOR(x)`	Round down to nearest integer
`ROUND`	`ROUND(x, d)`	Round to d decimal places
`TRUNC`	`TRUNC(x, d)`	Truncate to d decimal places
`SQRT`	`SQRT(x)`	Square root
`CBRT`	`CBRT(x)`	Cube root
`POWER`	`POWER(x, y)`	x raised to power y
`EXP`	`EXP(x)`	e^x
`LN`	`LN(x)`	Natural logarithm
`LOG`	`LOG(base, x)`	Logarithm with base
`LOG10`	`LOG10(x)`	Base-10 logarithm
`MOD`	`MOD(x, y)`	Modulo (remainder)
`SIGN`	`SIGN(x)`	Sign (-1, 0, or 1)
`PI`	`PI()`	π constant
`DEGREES`	`DEGREES(radians)`	Radians to degrees
`RADIANS`	`RADIANS(degrees)`	Degrees to radians
`GCD`	`GCD(a, b)`	Greatest common divisor
`LCM`	`LCM(a, b)`	Least common multiple
`SIN`	`SIN(x)`	Sine
`COS`	`COS(x)`	Cosine
`TAN`	`TAN(x)`	Tangent
`ASIN`	`ASIN(x)`	Arc sine
`ACOS`	`ACOS(x)`	Arc cosine
`ATAN`	`ATAN(x)`	Arc tangent
`ATAN2`	`ATAN2(y, x)`	Two-argument arc tangent
`GREATEST`	`GREATEST(a, b, ...)`	Largest value
`LEAST`	`LEAST(a, b, ...)`	Smallest value

Date/Time Functions

Function	Syntax	Description
`EXTRACT`	`EXTRACT(field FROM source)`	Extract date/time field
`DATE_PART`	`DATE_PART('field', source)`	Same as EXTRACT (function form)
`DATE_TRUNC`	`DATE_TRUNC('field', source)`	Truncate to specified precision
`NOW`	`NOW()`	Current timestamp
`CURRENT_DATE`	`CURRENT_DATE`	Current date
`CURRENT_TIMESTAMP`	`CURRENT_TIMESTAMP`	Current timestamp
`AGE`	`AGE(ts1, ts2)`	Interval between timestamps
`TO_CHAR`	`TO_CHAR(value, format)`	Format to string
`TO_DATE`	`TO_DATE(str, format)`	Parse date from string
`TO_TIMESTAMP`	`TO_TIMESTAMP(str, format)`	Parse timestamp from string
`MAKE_DATE`	`MAKE_DATE(year, month, day)`	Construct date
`MAKE_TIMESTAMP`	`MAKE_TIMESTAMP(y,m,d,h,m,s)`	Construct timestamp

EXTRACT fields: YEAR, MONTH, DAY, EPOCH, DOW (day of week), DOY (day of year), QUARTER, and HOUR, MINUTE, SECOND (timestamp/time source only, not a bare DATE).

WEEK is not a supported EXTRACT/DATE_PART field. To truncate to the start of the week, use DATE_TRUNC('week', source), which rounds down to the Monday of the containing week.

JSON Functions and Operators

Work with JSONB columns using operators and functions:

Operator / Function	Description
`json -> 'key'`	Get a field or element as JSON
`json ->> 'key'`	Get a field or element as text
`json #> '{a,b}'`	Get a nested value by path
`json @> other`	Does the left JSON contain the right?
`JSONB_BUILD_OBJECT(k, v, ...)`	Build a JSON object from key/value pairs
`JSONB_ARRAY_ELEMENTS(json)`	Expand a JSON array literal into rows (FROM clause only, see below)
`JSONB_EACH(json)`	Expand a JSON object literal into key/value rows (FROM clause only, see below)
`JSONB_EXTRACT_PATH(json, ...)`	Get a value at a path
`JSONB_SET(json, path, value)`	Return the JSON with a value replaced
`JSONB_ARRAY_LENGTH(json)`	Number of elements in a JSON array
`JSONB_TYPEOF(json)`	Type of the top-level JSON value

JSONB_ARRAY_ELEMENTS and JSONB_EACH only take a literal argument. They expand a JSON value you write directly in the query text, in the FROM clause:

SELECT * FROM JSONB_ARRAY_ELEMENTS('[1, 2, 3]');
SELECT * FROM JSONB_EACH('{"a": 1, "b": 2}');

They do not expand a stored column, and they cannot be used in the SELECT list:

-- Not supported: column argument, or expression position
SELECT JSONB_ARRAY_ELEMENTS(payload) FROM events;               -- errors
SELECT * FROM events, JSONB_ARRAY_ELEMENTS(events.payload);     -- errors

This is different from array UNNEST, which does expand a real column per row (see Array Functions below). If you need to expand JSONB stored in a column, extract the value into an application-level array first, or restructure the query so the JSON payload is a literal.

JSONB_OBJECT_KEYS, JSONB_ARRAY_ELEMENTS_TEXT, and JSONB_EACH_TEXT are not supported.

Array Functions

Function	Description
`UNNEST(array)`	Expand an array into rows
`ARRAY_AGG(expr)`	Collect values into an array (aggregate)
`ARRAY_LENGTH(array, dim)`	Length of an array dimension
`ARRAY_TO_STRING(array, sep)`	Join array elements into text

System Functions

Function	Description
`PG_TYPEOF(expr)`	Data type of an expression
`VERSION()`	Server version string
`CURRENT_USER`	Current role name
`CURRENT_DATABASE()`	Current database name
`CURRENT_SCHEMA()`	Current schema name

Aggregate Functions

Function	Syntax	Description
`COUNT`	`COUNT(*)` or `COUNT(expr)`	Count rows
`COUNT(DISTINCT)`	`COUNT(DISTINCT expr)`	Count distinct values
`SUM`	`SUM(expr)`	Sum of values
`AVG`	`AVG(expr)`	Average
`MIN`	`MIN(expr)`	Minimum value
`MAX`	`MAX(expr)`	Maximum value
`STDDEV` / `STDDEV_SAMP`	`STDDEV(expr)`	Sample standard deviation
`STDDEV_POP`	`STDDEV_POP(expr)`	Population standard deviation
`VARIANCE` / `VAR_SAMP`	`VARIANCE(expr)`	Sample variance
`VAR_POP`	`VAR_POP(expr)`	Population variance
`STRING_AGG`	`STRING_AGG(expr, delimiter)`	Concatenate with delimiter
`BOOL_AND` / `EVERY`	`BOOL_AND(expr)`	True if all true
`BOOL_OR`	`BOOL_OR(expr)`	True if any true

All aggregate functions support FILTER (WHERE ...) clause:

SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE status = 'active') AS active_count
FROM users;

Window Functions

Window functions operate over a partition of rows defined by OVER().

Ranking Functions

Function	Description
`ROW_NUMBER()`	Unique sequential number per partition
`RANK()`	Rank with gaps for ties
`DENSE_RANK()`	Rank without gaps for ties
`NTILE(n)`	Distribute rows into n buckets
`PERCENT_RANK()`	Relative rank (0 to 1)
`CUME_DIST()`	Cumulative distribution

Value Functions

Function	Description
`LAG(expr, offset, default)`	Value from preceding row
`LEAD(expr, offset, default)`	Value from following row
`FIRST_VALUE(expr)`	First value in window frame
`LAST_VALUE(expr)`	Last value in window frame
`NTH_VALUE(expr, n)`	Nth value in window frame

Aggregate Windows

All aggregate functions (SUM, COUNT, AVG, MIN, MAX) can be used as window functions:

SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS week_total,
    AVG(amount) OVER (PARTITION BY category ORDER BY date) AS running_avg
FROM sales;

Frame Clauses

-- Supported frame specifications
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW      -- default
ROWS BETWEEN N PRECEDING AND N FOLLOWING
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Analytics Packages

Beyond the built-in functions above, ScramDB ships 15 analytics packages, covering full-text search (fts, bm25), geospatial (geo, h3), vector and embedding math (embeddings), clustering and modeling (kmeans, dbscan, regression, anomaly), approximate aggregates (quantile, sketch, theta, topk), and data hygiene (anonymize, jsonschema). You can also write your own functions in JavaScript, TypeScript, Rust, Go, C, C++, Python, and Ruby (see Programmability for UDFs).

Installing a package registers its functions under their natural SQL signatures - one step, then call directly:

-- 1. Install the package (a registry coordinate, or the .afb bytes)
SELECT scram.install('scramdb/geo_haversine_distance');

-- 2. Call it
SELECT geo_haversine_distance(40.7128, -74.0060, 34.0522, -118.2437);

To bind a DIFFERENT name (or your own inline body) to an installed package, use CREATE OR REPLACE FUNCTION ... LANGUAGE js AS 'namespace/pkg'.

The .afb package files ship in the distribution's package directory, or you can build them yourself from the packages source tree. The inline-source form (LANGUAGE js AS $$...$$) and referencing an installed package by name (LANGUAGE js AS 'namespace/pkg') are both supported and tested. Use the package reference for anything built from a compiled language, and inline source when you are writing the body yourself.

The h3 package is a real, self-consistent hex-grid spatial index, but it is not bit-compatible with Uber's H3 format: it produces different cell IDs than industry- standard H3 tooling. Do not expect interoperability with existing H3 data.

Scalar Functions​

String Functions​

Math Functions​

Date/Time Functions​

JSON Functions and Operators​

Array Functions​

System Functions​

Aggregate Functions​

Window Functions​

Ranking Functions​

Value Functions​

Aggregate Windows​

Frame Clauses​

Analytics Packages​