Kraken.Core

Types

Array

opaque type Array a

Jsonb

opaque type Jsonb a

TypeTag

type TypeTag a =
  | TypeTag

A phantom carrier for a type, so a cast can name its target type without having a value of it.

Nullable

opaque type Nullable cols

A column scope on the nullable side of a left join.

Wraps a table's column record. Because the joined row may be absent, selecting a Nullable scope yields Maybe row rather than row.

Table

opaque type Table cols

ColRef

opaque type ColRef

An erased column reference (name + source) that drops the value type, so a primary key can name columns of differing types.

PrimaryKey

type PrimaryKey =
  | NoKey
  | Key ColRef
  | Composite (List ColRef)

A table's primary key, as declared by ColumnSet.primary_key.

Col

opaque type Col a

Generated

opaque type Generated a

A column the database fills in (SERIAL / identity / DEFAULT). Marking a schema column Generated a instead of Col a keeps it readable and usable in predicates exactly like a normal column, but the type prevents it from being supplied in an insert input — id-omission becomes a type guarantee, not a convention.

DefaultValue

type DefaultValue a =
  | UseDefault
  | Provide a

The insert value for a column that may use its table default. UseDefault renders SQL DEFAULT; Provide v binds an explicit value. This is different from Maybe: Nothing binds SQL NULL, while UseDefault asks the database to compute the column default.

GeneratedValue

type alias GeneratedValue a = DefaultValue a

SetExpr

opaque type SetExpr

One column = <expr> assignment in an upsert DO UPDATE SET. Build with assign.

ConflictTarget

type ConflictTarget =
  | OnColumns (List ColRef)
  | OnConstraint String

Where an ON CONFLICT detects a collision: a column list (on_columns [Db.ref u.id] → ON CONFLICT (id)) or a named constraint (on_constraint "users_email_key" → ON CONFLICT ON CONSTRAINT users_email_key).

SqlPart

type SqlPart =
  | Text String
  | Param Value

SqlFrag

record SqlFrag {
  parts: List SqlPart
}

Fragment

opaque type Fragment

Sql

opaque type Sql a

SqlArg

opaque type SqlArg

Expr

opaque type Expr

SelectItem

record SelectItem {
  expr: SqlFrag,
  alias: String,
  relabelable: Bool
}

Order

opaque type Order

Group

opaque type Group

Field

opaque type Field a

RelSlot

type RelSlot =
  | RelSlot Int Int

Locates a relation's parent-key column during the first decode pass: the relation's index (its slot, matching RelData) and the column offset of its key in the row. Both plain ints — the key value itself is read later, typed, from the raw rows.

RelData

opaque type RelData

Loaded children for each relation, keyed by relation index. The values are the one irreducible erasure: each is a Dict parent_key (List child) held opaquely because relations in one query have differing child types. Empty during the first pass.

Projection

opaque type Projection a

Preloaded

opaque type Preloaded out

A relation field in a select: it occupies one selected column (its parent key) but decodes to out — the children loaded by a separate query, shaped by how the relation is consumed (List child for a to-many preload, Maybe child for a to-one preload_one). Built by the query layer; selecting it contributes the parent-key column to the SELECT and a deferred decode that resolves children from the executor-supplied RelData.

Selection

type Selection =
  | Selection

Window

opaque type Window

A window specification — the OVER (…) frame. Build with window, then add partition_by / order_window. Reuses Db.group for PARTITION BY and Db.asc/Db.desc for the window's ORDER BY.

Traits

PgType

trait PgType a {
  fun encode_pg : a -> Value
  fun decode_pg : Decoder a
}

PgTypeName

trait PgTypeName a {
  fun pg_type_name : TypeTag a -> String
}

The Postgres type name a Saga type casts to (x::<name>). Only types you cast to need an instance.

ColumnSet

trait ColumnSet cols {
  fun columns : String -> cols
  fun column_names : cols -> List (String, String)
  fun primary_key : cols -> PrimaryKey
}

AsColRef

trait AsColRef c {
  fun as_col_ref : c -> ColRef
}

Columns (Col / Generated) that can be erased to a ColRef.

ToSql

trait ToSql input a {
  fun to_sql : input -> Sql a
}

ToArraySql

trait ToArraySql input a {
  fun to_array_sql : input -> Sql (Array a)
}

ToJsonbSql

trait ToJsonbSql input a {
  fun to_jsonb_sql : input -> Sql (Jsonb a)
}

Functions

array

fun array : List a -> Array a

array_to_list

fun array_to_list : Array a -> List a

jsonb

fun jsonb : a -> Jsonb a

jsonb_to_value

fun jsonb_to_value : Jsonb a -> a

encode_via

fun encode_via : a -> b -> a -> Value where {b: PgType}

Encode a by mapping it to a representation b that already has PgType. The forward direction is total: encode_via to_rep.

decode_via

fun decode_via : b -> Result a String -> Decoder a where {b: PgType}

Decode a from a representation b that has PgType, via a fallible back-mapping — a stored value may not correspond to any a (an unknown enum label, an unparseable number), so from_rep returns Result a String and the Err message surfaces as a decode failure.

enum_text_encode

fun enum_text_encode : List (a, String) -> a -> Value where {a: Eq}

Encode an enum-like type as text via an explicit value→label table, e.g. for a Postgres enum column (or a text column with fixed values). The table is the single source of truth for the wire labels — which can differ freely from the Saga constructor names (SCREAMING_SNAKE ↔ friendly). Pair with enum_text_decode over the same table in a tiny impl PgType:

type Status = Active | Archived deriving (Eq) status_labels = [(Active, "ACTIVE"), (Archived, "ARCHIVED")] impl PgType for Status { encode_pg = enum_text_encode status_labels decode_pg = enum_text_decode status_labels }

Panics if value is missing from the table (an incomplete mapping is a programmer error, not a data error).

enum_text_decode

fun enum_text_decode : List (a, String) -> Decoder a

Decode an enum-like type from text via an explicit value→label table (the inverse of enum_text_encode). An unrecognized label fails the decode rather than guessing.

as_nullable

fun as_nullable : cols -> Nullable cols

Mark a column scope as nullable. Used by left_join! to wrap the joined table's columns so that selecting the scope produces Maybe row.

unwrap_cols

fun unwrap_cols : Nullable cols -> cols

Unwrap a nullable scope to its underlying columns.

Use this only to reference a left-joined table's columns in post-join predicates and ordering (where_!, order_by!, having!) — most commonly an anti-join such as is_null (unwrap_cols p).id. SQL ignores result nullability in those positions, so the plain columns are appropriate.

Do not select the unwrapped column directly: that decodes it as non-null and turns absent joined rows into runtime decode errors. For scalar selections, use read_maybe; for whole rows, use read_nullable. Join ON clauses never need this — inner_join!/left_join! already pass their callback the plain columns.

key

fun key : c -> PrimaryKey where {c: AsColRef}

Name a single-column primary key (the common case): primary_key u = key u.id.

ref

fun ref : c -> ColRef where {c: AsColRef}

Erase a column to a ColRef, for assembling a composite key.

composite

fun composite : List ColRef -> PrimaryKey

Name a composite (multi-column) primary key.

primary_key_columns

fun primary_key_columns : PrimaryKey -> List String

The primary key's column names in order (empty for NoKey).

ref_name

fun ref_name : ColRef -> String

The column name a ColRef points to (e.g. for an ON CONFLICT target).

column_name

fun column_name : String -> c -> (String, String) where {c: AsColRef}

Pair a Saga field label with the SQL column name from a typed column handle. Use this in ColumnSet.column_names when the Saga field and SQL column differ: column_names a = [column_name "email" a.email].

default

fun default : DefaultValue a

Use the table's default for this column.

auto

fun auto : DefaultValue a

Let the database assign a generated column (the common case).

provide

fun provide : a -> DefaultValue a

Force a specific value into a defaulted or normally-generated column.

table

fun table : String -> Table cols where {cols: ColumnSet}

cte_table

fun cte_table : String -> String -> cols -> Table cols

Build a Table whose columns come from a supplied scope-builder rather than a ColumnSet — for CTEs and other derived sources, where the columns are whatever the subquery selected (named by make_scope at the reference alias). The result is referencable by from! / inner_join! / left_join! like any table.

table_as

fun table_as : String -> Table cols -> Table cols

table_name

fun table_name : Table cols -> String

table_alias

fun table_alias : Table cols -> Maybe String

table_cols

fun table_cols : String -> Table cols -> cols

col

fun col : String -> String -> Col a

generated

fun generated : String -> String -> Generated a

Declare a DB-generated column in a ColumnSet impl, mirroring col.

encode_value

fun encode_value : a -> Value where {a: PgType}

Encode a value into a Postgres bind parameter via its PgType instance.

col_name

fun col_name : Col a -> String

The bare column name (no table qualifier), e.g. for an UPDATE ... SET target.

generated_name

fun generated_name : Generated a -> String

excluded

fun excluded : Col a -> Col a

Reference a column on the EXCLUDED pseudo-table — the row proposed by the conflicting insert — inside an upsert DO UPDATE: Db.excluded u.name renders EXCLUDED.name. The existing row is referenced by the plain column (u.name).

assign

fun assign : Col a -> rhs -> SetExpr where {rhs: ToSql a}

Assign a column an arbitrary SQL expression in a DO UPDATE SET. The RHS is any ToSql value of the column's type, so it can combine the existing row, the proposed row, and literals: Db.assign u.count (Db.add_sql u.count (Db.excluded u.count)) → count = users.count + EXCLUDED.count. Listing only the columns you want also gives a partial DO UPDATE (a subset, not the blanket overwrite).

set_expr_column

fun set_expr_column : SetExpr -> String

set_expr_frag

fun set_expr_frag : SetExpr -> SqlFrag

on_columns

fun on_columns : List ColRef -> ConflictTarget

on_constraint

fun on_constraint : String -> ConflictTarget

expr_from_frag

fun expr_from_frag : SqlFrag -> Expr

Build a boolean Expr from a raw fragment. Used by the query layer to wrap a rendered subquery (e.g. EXISTS (…)) as a predicate; the fragment's Params are numbered by the enclosing query at render time.

empty_rel_data

fun empty_rel_data : RelData

The empty relation data used during the first decode pass (and by callers with no relations at all).

rel_data_of

fun rel_data_of : List (Int, Dynamic) -> RelData

Build relation data from (relation index, grouped-children-as-Dynamic) pairs.

rel_data_get

fun rel_data_get : Int -> RelData -> Maybe Dynamic

Look up a relation's grouped children (still opaque) by its index.

make_preloaded

fun make_preloaded : SelectItem -> RelData -> Int -> Dynamic -> Result (out, List RelSlot) DecodeError -> Preloaded out

Build a Preloaded field from its injected parent-key column and a resolver. The query layer supplies both; the resolver closes over the child types and the to-many/to-one shaping, so Preloaded is parameterized only by the output out.

map

fun map : a -> b -> Projection a -> Projection b

selection

fun selection : Selection

projection_into

fun projection_into : a -> b -> Projection (a -> b)

Start building a projection for a constructor. This is the explicit primitive used by build Selection Record { ... }.

projection_with

fun projection_with : Projection a -> Projection (a -> b) -> Projection b

Feed one projected value into a projected constructor. The constructor projection is the piped value:

build Selection User { id: Db.read u.id, name: Db.read u.name, }

projection_field

fun projection_field : String -> Projection a -> Projection (a -> b) -> Projection b

sql_text

fun sql_text : String -> Fragment

sql_param

fun sql_param : Value -> Fragment

sql_value

fun sql_value : a -> Fragment where {a: PgType}

sql_column

fun sql_column : Col a -> Fragment

sql_from_frag

fun sql_from_frag : SqlFrag -> Sql a where {a: PgType}

Wrap a raw fragment as a typed Sql a, decoding the result with a's default PgType decoder. Exposed so the query layer can present a rendered scalar subquery (SELECT … ) as a first-class Sql a.

sql

fun sql : input -> SqlArg where {input: ToSql a}

value

fun value : a -> SqlArg where {a: PgType}

lit

fun lit : a -> Sql a where {a: PgType + PgTypeName}

A typed literal as a first-class Sql a: a bound parameter with an explicit ::type cast ($1::text). Use it for literals in positions where Postgres can't infer the parameter type from context — concat, case_when branches, a bare select of a constant — which otherwise fail with "could not determine data type of parameter". In a comparison the other operand supplies the type, so Db.value / the eq family don't need this.

raw

fun raw : String -> List SqlArg -> Sql a where {a: PgType}

raw_array

fun raw_array : String -> List SqlArg -> Sql (Array a) where {a: PgType}

raw_array_like

fun raw_array_like : Col (Array a) -> String -> List SqlArg -> Sql (Array a) where {a: PgType}

expr_raw

fun expr_raw : String -> List SqlArg -> Expr

expr

fun expr : Sql Bool -> Expr

expr_frag

fun expr_frag : Expr -> SqlFrag

frag_of

fun frag_of : input -> SqlFrag where {input: ToSql a}

The SQL fragment of any ToSql value (a column, raw expression, aggregate, …). Exposed so the query layer can build predicates like IN (subquery) from a left-hand value and a rendered subquery.

cast

fun cast : input -> Sql b where {input: ToSql a, b: PgType + PgTypeName}

A SQL cast: render (<input>)::<target> and decode as the target type. The result is a first-class Sql b usable in select / predicates. This is a typed assertion, not a checked conversion — you vouch that the cast yields a b (Postgres validates at runtime, like raw); Kraken just renders ::b and decodes accordingly. The target b must be annotated or fixed by an as_* helper, since it isn't determined by the input.

as_int

fun as_int : input -> Sql Int where {input: ToSql a}

<input>::integer.

as_float

fun as_float : input -> Sql Float where {input: ToSql a}

<input>::double precision.

as_text

fun as_text : input -> Sql String where {input: ToSql a}

<input>::text — e.g. a timestamp or number rendered as a string.

as_bool

fun as_bool : input -> Sql Bool where {input: ToSql a}

<input>::boolean.

as_timestamp

fun as_timestamp : input -> Sql NaiveDateTime where {input: ToSql a}

<input>::timestamp — e.g. parse a string column as a timestamp.

as_date

fun as_date : input -> Sql Date where {input: ToSql a}

<input>::date.

as_time

fun as_time : input -> Sql Time where {input: ToSql a}

<input>::time.

sql_fn

fun sql_fn : String -> List SqlArg -> Sql a where {a: PgType}

A generic SQL function call: name(arg1, arg2, …). Arguments are SqlArgs, so pass columns/expressions with Db.sql and literals with Db.value. The result type isn't inferable from the arguments, so annotate it or let the selection fix it: Db.sql_fn "lower" [Db.sql u.name] : Db.Sql String.

coalesce

fun coalesce : input -> a -> Sql a where {input: ToSql a, a: PgType}

COALESCE(<input>, <fallback>) — the first non-NULL of the two. The principled way to recover a non-null scalar from a left join: after Db.unwrap_cols, a left-joined column reads as Col a, and coalesce pairs it with a default of the same type, yielding a non-null Sql a you can put in select (Db.coalesce (Db.unwrap_cols p).count 0 → COALESCE(t1.count, $1)).

coalesce_sql

fun coalesce_sql : left -> right -> Sql a where {left: ToSql a, right: ToSql a, a: PgType}

COALESCE(<left>, <right>) over two SQL values of the same type (e.g. two columns) rather than a literal fallback.

not_

fun not_ : Expr -> Expr

Boolean negation: NOT (<expr>). Complements and_ / or_ / is_null.

lower

fun lower : input -> Sql String where {input: ToSql String}

LOWER(<input>).

upper

fun upper : input -> Sql String where {input: ToSql String}

UPPER(<input>).

trim

fun trim : input -> Sql String where {input: ToSql String}

TRIM(<input>).

concat

fun concat : List SqlArg -> Sql String

CONCAT(arg1, arg2, …) — NULL arguments are treated as empty (unlike ||). Arguments are SqlArgs (Db.sql for columns, Db.value for literals).

add

fun add : input -> a -> Sql a where {input: ToSql a, a: PgType}

(<input> + <literal>) — e.g. the upsert idiom count = users.count + 1.

sub

fun sub : input -> a -> Sql a where {input: ToSql a, a: PgType}

(<input> - <literal>).

mul

fun mul : input -> a -> Sql a where {input: ToSql a, a: PgType}

(<input> * <literal>).

div

fun div : input -> a -> Sql a where {input: ToSql a, a: PgType}

(<input> / <literal>).

add_sql

fun add_sql : left -> right -> Sql a where {left: ToSql a, right: ToSql a, a: PgType}

(<left> + <right>) between two SQL values (e.g. price + tax).

sub_sql

fun sub_sql : left -> right -> Sql a where {left: ToSql a, right: ToSql a, a: PgType}

(<left> - <right>).

mul_sql

fun mul_sql : left -> right -> Sql a where {left: ToSql a, right: ToSql a, a: PgType}

(<left> * <right>) — e.g. price * quantity.

div_sql

fun div_sql : left -> right -> Sql a where {left: ToSql a, right: ToSql a, a: PgType}

(<left> / <right>).

case_when

fun case_when : List (Expr, SqlArg) -> SqlArg -> Sql a where {a: PgType}

CASE WHEN <cond> THEN <val> … ELSE <else> END. Each branch pairs a predicate with a SqlArg result; the else is the fallback SqlArg. Branch and else values use Db.sql (columns/expressions) or Db.value (literals). The result type is fixed by annotation or the selection. Panics with no branches.

eq

fun eq : input -> a -> Expr where {input: ToSql a, a: PgType}

not_eq

fun not_eq : input -> a -> Expr where {input: ToSql a, a: PgType}

gt

fun gt : input -> a -> Expr where {input: ToSql a, a: PgType}

gte

fun gte : input -> a -> Expr where {input: ToSql a, a: PgType}

lt

fun lt : input -> a -> Expr where {input: ToSql a, a: PgType}

lte

fun lte : input -> a -> Expr where {input: ToSql a, a: PgType}

eq_sql

fun eq_sql : left -> right -> Expr where {left: ToSql a, right: ToSql a, a: PgType}

eq_col

fun eq_col : left -> right -> Expr where {left: ToSql a, right: ToSql a, a: PgType}

not_eq_sql

fun not_eq_sql : left -> right -> Expr where {left: ToSql a, right: ToSql a, a: PgType}

Comparisons between two SQL values (columns, expressions, scalar subqueries) rather than against a literal. The eq family compares against a literal RHS; these compare two ToSql operands of the same element type — e.g. Db.gt_sql u.age (Db.scalar_subquery …).

gt_sql

fun gt_sql : left -> right -> Expr where {left: ToSql a, right: ToSql a, a: PgType}

gte_sql

fun gte_sql : left -> right -> Expr where {left: ToSql a, right: ToSql a, a: PgType}

lt_sql

fun lt_sql : left -> right -> Expr where {left: ToSql a, right: ToSql a, a: PgType}

lte_sql

fun lte_sql : left -> right -> Expr where {left: ToSql a, right: ToSql a, a: PgType}

like

fun like : input -> String -> Expr where {input: ToSql String}

ilike

fun ilike : input -> String -> Expr where {input: ToSql String}

between

fun between : input -> a -> a -> Expr where {input: ToSql a, a: PgType}

contains

fun contains : input -> Array a -> Expr where {input: ToArraySql a, a: PgType}

contained_by

fun contained_by : input -> Array a -> Expr where {input: ToArraySql a, a: PgType}

overlaps

fun overlaps : input -> Array a -> Expr where {input: ToArraySql a, a: PgType}

json_contains

fun json_contains : input -> Jsonb a -> Expr where {input: ToJsonbSql a, a: ToJson + FromJson}

json_has_key

fun json_has_key : input -> String -> Expr where {input: ToJsonbSql a}

json_text

fun json_text : input -> String -> Sql String where {input: ToJsonbSql a}

in_

fun in_ : input -> List a -> Expr where {input: ToSql a, a: PgType}

not_in

fun not_in : input -> List a -> Expr where {input: ToSql a, a: PgType}

eq_any

fun eq_any : input -> List a -> Expr where {input: ToSql a, a: PgType}

not_eq_all

fun not_eq_all : input -> List a -> Expr where {input: ToSql a, a: PgType}

like_any

fun like_any : input -> List String -> Expr where {input: ToSql String}

ilike_any

fun ilike_any : input -> List String -> Expr where {input: ToSql String}

is_null

fun is_null : input -> Expr where {input: ToSql a, a: PgType}

is_not_null

fun is_not_null : input -> Expr where {input: ToSql a, a: PgType}

and_

fun and_ : List Expr -> Expr

or_

fun or_ : List Expr -> Expr

nullable_row

fun nullable_row : Projection a -> Projection (Maybe a)

Adapt a whole-row projection into its nullable form for left joins.

If every column the projection spans is SQL NULL in a result row, it decodes to Nothing; otherwise the row is decoded and wrapped in Just. The all-NULL rule remains correct even when a domain field is itself nullable, since a matched row still carries at least one non-null required column.

projection_relabel

fun projection_relabel : String -> Projection a -> Projection a

Relabel a projection for a record field. This is the explicit primitive used by build Selection Record { field: projection }: single-column fields take the field label directly, while multi-column fields get prefixed labels like user_id.

projection_selections

fun projection_selections : Projection a -> List SelectItem

make_projection

fun make_projection : List SelectItem -> Int -> RelData -> Int -> Dynamic -> Result (a, List RelSlot) DecodeError -> Projection a

Build a Projection from explicit selections, width, and a relation-aware decoder. Exposed so the query layer can construct a Preloaded field's projection (its single parent-key column plus a deferred child-resolving decode).

as_sql

fun as_sql : input -> Sql a where {input: ToSql a}

Coerce any column-like value (Col / Generated / Sql) to a Sql a. Exposed so the query layer can accept a relation key as a column accessor regardless of whether the schema field is a plain or generated column.

read

fun read : input -> Projection a where {input: ToSql a}

Project any SQL-like value (Col, Generated, or Sql) as a single decoded column. This is the explicit leaf used by projection_into / projection_with.

read_sql

fun read_sql : Sql a -> Projection a

Project an already-built SQL expression. Alias is supplied by the surrounding projection construction or by the generated select item.

read_nullable

fun read_nullable : Nullable cols -> cols -> Projection a -> Projection (Maybe a)

Project a nullable scope through a whole-row projection. This keeps the left join all-NULL sentinel in one named operation instead of making callers unwrap a scope directly at a select site.

read_maybe

fun read_maybe : Nullable cols -> cols -> input -> Projection (Maybe a) where {input: ToSql a}

Project one SQL-like value from a nullable scope as Maybe a. This is the scalar sibling of read_nullable: Db.read_maybe p (fun p -> p.title) decodes Maybe String.

read_preloaded

fun read_preloaded : Preloaded out -> Projection out

Project a preloaded relation field. It contributes the hidden parent-key column and decodes through the relation data stitched in by the query executor.

col_select_item

fun col_select_item : String -> Sql a -> SelectItem

A SelectItem selecting a single Sql value under the given alias. Exposed so the query layer can inject a relation's parent-key column into the SELECT.

col_decoder

fun col_decoder : Sql a -> Int -> Dynamic -> Result a DecodeError

A positional decoder for a single Sql value, using its own decoder. Exposed so the query layer can read a relation's parent-key value at decode time.

col_projection

fun col_projection : Sql a -> Projection a

A single-column Projection for a Sql value. Exposed so the query layer can pair a relation's foreign-key column alongside the child row.

project_pair

fun project_pair : Projection a -> Projection b -> Projection (a, b)

Pair two projections into one yielding a tuple (selections concatenated, widths summed, decodes threaded). Exposed for relation loading's (fk, child) rows.

decode_projection

fun decode_projection : Projection a -> RelData -> Dynamic -> Result (a, List RelSlot) DecodeError

Decode a result row through a projection, threading relation data and collecting any relation keys the row's Preloaded slots emit. Callers with no relations pass empty_rel_data and ignore the (empty) key list.

asc

fun asc : input -> Order where {input: ToSql a, a: PgType}

desc

fun desc : input -> Order where {input: ToSql a, a: PgType}

group

fun group : input -> Group where {input: ToSql a, a: PgType}

order_expr

fun order_expr : Order -> SqlFrag

order_direction

fun order_direction : Order -> String

group_frag

fun group_frag : Group -> SqlFrag

count_star

fun count_star : Sql Int

count

fun count : input -> Sql Int where {input: ToSql a, a: PgType}

count_distinct

fun count_distinct : input -> Sql Int where {input: ToSql a, a: PgType}

sum

fun sum : input -> Sql (Maybe a) where {input: ToSql a, a: PgType}

avg

fun avg : input -> Sql (Maybe Float) where {input: ToSql a, a: PgType}

min

fun min : input -> Sql (Maybe a) where {input: ToSql a, a: PgType}

max

fun max : input -> Sql (Maybe a) where {input: ToSql a, a: PgType}

window

fun window : Window

An empty window (OVER ()). Refine it with partition_by / order_window.

partition_by

fun partition_by : List Group -> Window -> Window

Set the window's PARTITION BY keys.

order_window

fun order_window : List Order -> Window -> Window

Set the window's ORDER BY terms (needed for ranking / running aggregates).

over

fun over : Sql a -> Window -> Sql a

Turn any Sql a into its windowed form: <expr> OVER (<window>). Most useful on an aggregate for a running / partitioned total — Db.over (Db.sum u.amount) (Db.partition_by [Db.group u.dept] Db.window) → SUM(u.amount) OVER (PARTITION BY u.dept). The decoder is preserved, so a windowed sum is still Sql (Maybe a).

row_number

fun row_number : Window -> Sql Int

ROW_NUMBER() OVER (<window>) — sequential row number within each partition.

rank

fun rank : Window -> Sql Int

RANK() OVER (<window>) — rank with gaps after ties.

dense_rank

fun dense_rank : Window -> Sql Int

DENSE_RANK() OVER (<window>) — rank without gaps after ties.

lag

fun lag : input -> Window -> Sql (Maybe a) where {input: ToSql a, a: PgType}

LAG(<input>) OVER (<window>) — the value from the previous row in the window (NULL on the first row, hence Maybe a).

lead

fun lead : input -> Window -> Sql (Maybe a) where {input: ToSql a, a: PgType}

LEAD(<input>) OVER (<window>) — the value from the next row in the window (NULL on the last row, hence Maybe a).