#! Implementation of `std` module.
#!
#! This file is not really PRQL.
#! It can contain only:
#! - functions declarations that don't have named params and s-string-only body,
#! - module declarations whose names correspond to sql dialect names.
#!
#! Functions can define `binding_strength` annotation, which signifies how much
#! precedence does the top-level operation in the s-string provide.
#! This value defaults to 100 (high precedence).
#!
#! S-strings can define required binding strength of the interpolated expression.
#! This value defaults to binding strength of the function.
# Aggregation functions
@{window_frame=true}
let min = column -> s"MIN({column:0})"
@{window_frame=true}
let max = column -> s"MAX({column:0})"
@{window_frame=true, coalesce="0"}
let sum = column -> s"SUM({column:0})"
@{window_frame=true}
let average = column -> s"AVG({column:0})"
@{window_frame=true}
let stddev = column -> s"STDDEV({column:0})"
@{window_frame=true, coalesce="TRUE"}
let all = column -> s"BOOL_AND({column:0})"
@{window_frame=true, coalesce="FALSE"}
let any = column -> s"BOOL_OR({column:0})"
@{window_frame=true, coalesce="''"}
let concat_array = column -> s"STRING_AGG({column:0}, '')"
@{window_frame=true}
let count = column -> s"COUNT(*)"
@{window_frame=true}
let count_distinct = column -> s"COUNT(DISTINCT {column:0})"
# Window functions
let lag = offset column -> s"LAG({column:0}, {offset:0})"
let lead = offset column -> s"LEAD({column:0}, {offset:0})"
let first = column -> s"FIRST_VALUE({column:0})"
let last = column -> s"LAST_VALUE({column:0})"
let rank = -> s"RANK()"
let rank_dense = -> s"DENSE_RANK()"
let row_number = -> s"ROW_NUMBER()"
# Mathematical functions
module math {
# Clickhouse: https://clickhouse.com/docs/en/sql-reference/functions/math-functions
# DuckDB: https://duckdb.org/docs/test/functions/math.html
# MariaDB: https://mariadb.com/kb/en/numeric-functions/
# MySQL: https://dev.mysql.com/doc/refman/8.4/en/mathematical-functions.html
# Postgres: https://www.postgresql.org/docs/current/functions-math.html
# SQLite: https://www.sqlite.org/lang_mathfunc.html
# MSSQL: https://learn.microsoft.com/en-us/sql/t-sql/functions/mathematical-functions-transact-sql
# BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions
# Snowflake: https://docs.snowflake.com/en/sql-reference/functions-math.html
let abs = column -> s"ABS({column:0})"
let floor = column -> s"FLOOR({column:0})"
let ceil = column -> s"CEIL({column:0})"
let pi = -> s"PI()"
let exp = column -> s"EXP({column:0})"
let ln = column -> s"LN({column:0})"
let log10 = column -> s"LOG10({column:0})"
let log = base column -> s"LOG10({column:0}) / LOG10({base:0})"
let sqrt = column -> s"SQRT({column:0})"
let degrees = column -> s"DEGREES({column:0})"
let radians = column -> s"RADIANS({column:0})"
let cos = column -> s"COS({column:0})"
let acos = column -> s"ACOS({column:0})"
let sin = column -> s"SIN({column:0})"
let asin = column -> s"ASIN({column:0})"
let tan = column -> s"TAN({column:0})"
let atan = column -> s"ATAN({column:0})"
# Note exponent goes first, so `pow 2 3` is 2^3
let pow = exponent column -> s"POW({column:0}, {exponent:0})"
let round = n_digits column -> s"ROUND({column:0}, {n_digits:0})"
}
# Other functions
let as = `type` column -> s"CAST({column:0} AS {type:0})"
# Text functions
module text {
# Clickhouse: https://clickhouse.com/docs/en/sql-reference/functions/string-functions
# DuckDB: https://duckdb.org/docs/sql/functions/char
# MariaDB: https://mariadb.com/kb/en/string-functions/
# MySQL: https://dev.mysql.com/doc/refman/8.4/en/string-functions.html
# Postgres: https://www.postgresql.org/docs/current/functions-string.html
# SQLite: https://www.sqlite.org/lang_corefunc.html
# MSSQL: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-functions-transact-sql
# BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions
# Snowflake: https://docs.snowflake.com/en/sql-reference/functions-string
let lower = column -> s"LOWER({column:0})"
let upper = column -> s"UPPER({column:0})"
let ltrim = column -> s"LTRIM({column:0})"
let rtrim = column -> s"RTRIM({column:0})"
let trim = column -> s"TRIM({column:0})"
let length = column -> s"CHAR_LENGTH({column:0})"
let extract = offset length column -> s"SUBSTRING({column:0}, {offset:0}, {length:0})"
let replace = pattern replacement column -> s"REPLACE({column:0}, {pattern:0}, {replacement:0})"
let starts_with = prefix column -> s"{column:0} LIKE CONCAT({prefix:0}, '%')"
let contains = substr column -> s"{column:0} LIKE CONCAT('%', {substr:0}, '%')"
let ends_with = suffix column -> s"{column:0} LIKE CONCAT('%', {suffix:0})"
}
# Source-reading functions, primarily for DuckDB
let read_parquet = binary_as_string file_row_number hive_partitioning union_by_name source -> s"read_parquet({source:0})"
let read_csv = source -> s"read_csv({source:0})"
let read_json = source -> s"read_json({source:0})"
@{binding_strength=11}
let mul = l r -> null
@{binding_strength=100}
let div_i = l r -> s"FLOOR(ABS({l:11} / {r:12})) * SIGN({l:0}) * SIGN({r:0})"
# We have a simple float division by default, but it can be overridden by
# dialects.
# Note that this uses `12` for the RHS binding strength, which is one more than
# binding strength of division. That's because we don't use associativity here,
# and so we need to make sure that the RHS is parenthesized when the binding
# strengths are equal; e.g. in `a / (b / c)`.
@{binding_strength=11}
let div_f = l r -> s"{l} / {r:12}"
@{binding_strength=11}
let mod = l r -> s"{l} % {r:12}"
@{binding_strength=10}
let add = l r -> null
@{binding_strength=10}
let sub = l r -> null
@{binding_strength=6}
let eq = l r -> null
@{binding_strength=6}
let ne = l r -> null
@{binding_strength=6}
let gt = l r -> null
@{binding_strength=6}
let lt = l r -> null
@{binding_strength=6}
let gte = l r -> null
@{binding_strength=6}
let lte = l r -> null
@{binding_strength=3}
let and = l r -> null
@{binding_strength=2}
let or = l r -> null
let coalesce = l r -> s"COALESCE({l:0}, {r:0})"
let regex_search = text pattern -> s"REGEXP({text:0}, {pattern:0})"
@{binding_strength=13}
let neg = l -> s"-{l}"
@{binding_strength=4}
let not = l -> s"NOT {l}"
module ansi {
@{binding_strength=11}
let div_f = l r -> s"({l} * 1.0 / {r:12})"
}
module bigquery {
@{binding_strength=11}
let div_f = l r -> s"({l} * 1.0 / {r:12})"
# Mathematical functions
module math {
# BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions
let degrees = column -> s"({column:0} * 180 / PI())"
let radians = column -> s"({column:0} * PI() / 180)"
}
module date {
let to_text = format column -> s"FORMAT_TIMESTAMP({format:0}, CAST({column:0} AS TIMESTAMP))"
}
let regex_search = text pattern -> s"REGEXP_CONTAINS({text:0}, {pattern:0})"
}
module clickhouse {
# https://clickhouse.com/docs/en/sql-reference/functions/arithmetic-functions#divide
@{binding_strength=11}
let div_f = l r -> s"({l} / {r:12})"
@{binding_strength=11}
let div_i = l r -> s"({l} DIV {r:12})"
# Date functions
module date {
# https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions
let to_text = format column -> s"formatDateTimeInJodaSyntax({column:0}, {format:0})"
}
let regex_search = text pattern -> s"match({text:0}, {pattern:0})"
let read_csv = source -> s"file({source:0}, 'CSV')"
let read_json = source -> s"file({source:0}, 'Json')"
let read_parquet = source -> s"file({source:0}, 'Parquet')"
}
module duckdb {
@{binding_strength=11}
let div_f = l r -> s"({l} / {r:12})"
@{binding_strength=11}
let div_i = l r -> s"TRUNC({l:11} / {r:12})"
# Text functions
module text {
# DuckDB: https://duckdb.org/docs/sql/functions/char
let length = column -> s"LENGTH({column:0})"
}
# Date functions
module date {
# https://duckdb.org/docs/sql/functions/dateformat
let to_text = format column -> s"strftime({column:0}, {format:0})"
}
let regex_search = text pattern -> s"REGEXP_MATCHES({text:0}, {pattern:0})"
let read_csv = source -> s"read_csv_auto({source:0})"
let read_json = source -> s"read_json_auto({source:0})"
let read_parquet = binary_as_string file_row_number hive_partitioning union_by_name source -> s"read_parquet({source:0}, binary_as_string={binary_as_string:0}, file_row_number={file_row_number:0}, hive_partitioning={hive_partitioning:0}, union_by_name={union_by_name:0})"
}
module mssql {
@{binding_strength=11}
let div_f = l r -> s"({l} * 1.0 / {r:12})"
# Mathematical functions
module math {
# https://learn.microsoft.com/en-us/sql/t-sql/functions/mathematical-functions-transact-sql
let ceil = column -> s"CEILING({column:0})"
let ln = column -> s"LOG({column:0})"
let pow = exponent column -> s"POWER({column:0}, {exponent:0})"
}
# Text functions
module text {
# https://learn.microsoft.com/en-us/sql/t-sql/functions/string-functions-transact-sql
let length = column -> s"LEN({column:0})"
}
# Date functions
module date {
# https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql
let to_text = format column -> s"FORMAT({column:0}, {format:0})"
}
let regex_search = text pattern -> null
}
module mysql {
@{binding_strength=11}
let div_f = l r -> s"({l} / {r:12})"
@{binding_strength=11}
let div_i = l r -> s"({l} DIV {r:12})"
@{binding_strength=100}
let mod = l r -> s"ROUND(MOD({l:0}, {r:0}))"
# Date functions
module date {
# https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html
let to_text = format column -> s"DATE_FORMAT({column:0}, {format:0})"
}
# 'c' for case-sensitive
let regex_search = text pattern -> s"REGEXP_LIKE({text:0}, {pattern:0}, 'c')"
}
module postgres {
@{binding_strength=11}
let div_f = l r -> s"({l} * 1.0 / {r:12})"
@{binding_strength=100}
let div_i = l r -> s"TRUNC({l:11} / {r:12})"
# Mathematical functions
module math {
# Postgres: https://www.postgresql.org/docs/7.4/functions-math.html
@{binding_strength=100}
let round = n_digits column -> s"ROUND(({column:0})::numeric, {n_digits:0})"
}
# Text functions
module text {
# Postgres: https://www.postgresql.org/docs/7.4/functions-string.html
let extract = offset length column -> s"SUBSTR({column:0}, {offset:0}, {length:0})"
}
# Date functions
module date {
# https://www.postgresql.org/docs/current/functions-formatting.html
let to_text = format column -> s"TO_CHAR({column:0}, {format:0})"
}
@{binding_strength=9}
let regex_search = text pattern -> s"{text} ~ {pattern}"
}
module redshift {
@{binding_strength=11}
let div_f = l r -> s"({l} * 1.0 / {r:12})"
# Text functions
module text {
# https://docs.aws.amazon.com/redshift/latest/dg/r_concat_op.html
let contains = substr column -> s"{column:0} LIKE '%' || {substr:0} || '%'"
}
}
module glaredb {
@{binding_strength=11}
let div_f = l r -> s"({l} * 1.0 / {r:12})"
@{binding_strength=100}
let div_i = l r -> s"TRUNC({l:11} / {r:12})"
# Mathematical functions
module math {
# GlareDB: https://glaredb.com/docs/sql-reference/functions/math-functions
@{binding_strength=100}
let round = n_digits column -> s"ROUND(({column:0})::numeric, {n_digits:0})"
}
@{binding_strength=9}
let regex_search = text pattern -> s"{text} ~ {pattern}"
let read_csv = source -> s"csv_scan({source:0})"
let read_parquet = source -> s"parquet_scan({source:0})"
}
module sqlite {
@{window_frame=true, coalesce="TRUE", binding_strength=6}
let all = column -> s"MIN({column:0}) > 0"
@{window_frame=true, coalesce="FALSE", binding_strength=6}
let any = column -> s"MAX({column:0}) > 0"
@{window_frame=true, coalesce="''"}
let concat_array = column -> s"GROUP_CONCAT({column:0}, '')"
@{binding_strength=11}
let div_f = l r -> s"({l} * 1.0 / {r:12})"
@{binding_strength=100}
let div_i = l r -> s"ROUND(ABS({l:11} / {r:12}) - 0.5) * SIGN({l:0}) * SIGN({r:0})"
# Text functions
module text {
# SQLite: https://www.sqlite.org/lang_corefunc.html
let length = column -> s"LENGTH({column:0})"
let starts_with = prefix column -> s"{column:0} LIKE {prefix:0} || '%'"
let contains = substr column -> s"{column:0} LIKE '%' || {substr:0} || '%'"
let ends_with = suffix column -> s"{column:0} LIKE '%' || {suffix:0}"
}
@{binding_strength=9}
let regex_search = text pattern -> s"{text} REGEXP {pattern}"
}
module snowflake {
# https://docs.snowflake.com/en/sql-reference/operators-arithmetic#division
@{binding_strength=11}
let div_f = l r -> s"({l} / {r:12})"
# Text functions
module text {
# https://docs.snowflake.com/en/sql-reference/functions-string
let length = column -> s"LENGTH({column:0})"
}
}