Standard library

The standard library currently contains commonly used functions that are used in SQL. It’s not yet as broad as we’d like, and we’re very open to expanding it.

Currently s-strings are an escape-hatch for any function that isn’t in our standard library. If we find ourselves using them for something frequently, raise an issue and we’ll add it to the stdlib.

Here’s the source of the current PRQL std:

Note

PRQL 0.9.0 has started supporting different DB implementations for standard library functions. The source is the std.sql.

# The PRQL standard library defines the following functions and transforms.
# The definitions are whitespace insensitive, and have this form:
#
#

let my_func = param1 param2 … -> body_expr

```

#

Where:

* my_func is the name of the function

* param1 is the first parameter optionally followed by a type in “< … >”

* param2 etc. follow the same pattern as param1

* <return_type> is the type of result wrapped in “< … >”

* body_expr defines the function body that creates the result.

It can be PRQL code or internal ... to indicate internal compiler code.

Operators

let mul = left right -> internal std.mul let div_i = left right -> internal std.div_i let div_f = left right -> internal std.div_f let mod = left right -> internal std.mod let add = left right -> internal std.add let sub = left right -> internal std.sub let eq = left right -> internal std.eq let ne = left right -> internal std.ne let gt = left right -> internal std.gt let lt = left right -> internal std.lt let gte = left right -> internal std.gte let lte = left right -> internal std.lte let and = left right -> internal std.and let or = left right -> internal std.or let coalesce = left right -> internal std.coalesce let regex_search = text pattern -> internal std.regex_search

let neg = expr -> internal std.neg let not = expr -> internal std.not

Types

Type primitives

type int type float type bool type text type date type time type timestamp type func

Generic array

TODO: an array of anything, not just nulls

type array = [null]

Scalar

type scalar = int || float || bool || text || date || time || timestamp || null type tuple_of_scalars = {scalar..}

Relation (an array of tuples)

type relation = [tuple_of_scalars]

Transform

type transform = (func relation -> relation)

Functions

Relational transforms

let from = func default_db.source -> internal from

let select = func columns tbl -> internal select

let filter = func condition tbl -> internal filter

let derive = func columns tbl -> internal derive

let aggregate = func columns tbl -> internal aggregate

let sort = func by tbl -> internal sort

let take = func expr tbl -> internal take

let join = func default_db.with condition noresolve.side:inner tbl -> internal join

let group = func by pipeline tbl -> internal group

let window = func rows:0..0 range:0..0 expanding :false rolling :0 pipeline tbl -> internal window

let append = default_db.bottom top -> internal append let intersect = default_db.bottom top -> ( t = top join (b = bottom) (tuple_every (tuple_map _eq (tuple_zip t. b.))) select t. ) let remove = default_db.bottom top -> ( t = top join side:left (b = bottom) (tuple_every (tuple_map _eq (tuple_zip t. b.))) filter (tuple_every (tuple_map _is_null b.)) select t.* ) let loop = func pipeline top -> internal loop

Aggregate functions

These return either a scalar when used within aggregate, or a column when used anywhere else.

let min = column -> internal std.min

let max = column -> internal std.max

let sum = column -> internal std.sum

let average = column -> internal std.average

let stddev = column -> internal std.stddev

let every = column -> internal std.every

let any = column -> internal std.any

let concat_array = column -> internal std.concat_array

Counts number of items in the column.

Note that the count will include null values.

let count = column -> internal std.count

Deprecated in favour of filterning input to the [std.count] function (not yet implemented).

@{deprecated} let count_distinct = column -> internal std.count_distinct

Window functions

let lag = offset column -> internal std.lag let lead = offset column -> internal std.lead let first = column -> internal std.first let last = column -> internal std.last let rank = column -> internal std.rank let rank_dense = column -> internal std.rank_dense let row_number = column -> internal std.row_number

Misc functions

let round = n_digits column -> internal std.round let as = noresolve.type column -> internal std.as let in = pattern value -> internal in

Tuple functions

let tuple_every = func list -> internal tuple_every let tuple_map = func fn list -> internal tuple_map let tuple_zip = func a b -> internal tuple_zip let _eq = func a -> internal _eq let _is_null = func a -> _param.a == null

Misc

let from_text = input noresolve.format:csv -> internal from_text

String functions

let lower = column -> internal std.lower let upper = column -> internal std.upper

File-reading functions, primarily for DuckDB

let read_parquet = source -> internal std.read_parquet let read_csv = source -> internal std.read_csv

And a couple of examples:
#### [PRQL](#prql)

from employees derive { gross_salary = (salary + payroll_tax | as int), gross_salary_rounded = (gross_salary | round 0), time = s”NOW()”, # an s-string, given no now function exists in PRQL }

#### [SQL](#sql)

SELECT *, CAST(salary + payroll_tax AS int) AS gross_salary, ROUND(CAST(salary + payroll_tax AS int), 0) AS gross_salary_rounded, NOW() AS time FROM employees

Example of different implementations of division and integer division:
#### [PRQL](#prql-1)

prql target:sql.sqlite

from [{x = 13, y = 5}] select { quotient = x / y, int_quotient = x // y, }

#### [SQL](#sql-1)

WITH table_0 AS ( SELECT 13 AS x, 5 AS y ) SELECT (x 1.0 / y) AS quotient, ROUND(ABS(x / y) - 0.5) SIGN(x) * SIGN(y) AS int_quotient FROM table_0

#### [PRQL](#prql-2)

prql target:sql.mysql

from [{x = 13, y = 5}] select { quotient = x / y, int_quotient = x // y, }

#### [SQL](#sql-2)

WITH table_0 AS ( SELECT 13 AS x, 5 AS y ) SELECT (x / y) AS quotient, (x DIV y) AS int_quotient FROM table_0

```