IF Commons GitHub

sql/extensions/routines/plpgsql

Written to: 3 requirements: 3 proved, 0 partly, 0 failing, 0 with no test recorded. 0050-sql-plpgsql FR-001, 0050-sql-plpgsql FR-002, 0050-sql-plpgsql FR-003

Typed PL/pgSQL (commons data 0050-sql-plpgsql): the body of a routine written as variables and statements (assign, run, if, while, for each row, raise, return, return a query, try with handlers) and not as text. A variable has a type and a value of that type; a reference to a table, a column, a parameter or a variable is a value, written by the case style; and the block is checked before it is made: a variable that was not declared, a function that can come to its end without returning, a value returned from a procedure, a loop that names a table the schema lacks, are errors as values. The result is the body of a routine definition (language: "plpgsql"), so everything the routines extension checks is checked too.

Source: commons/data/sql/extensions/routines/plpgsql.ts

21 of 29 exports are imported by a test of the run. A test that imports an export may not call it or check what it returns.

attemptconst
const attempt: (body: readonly Stmt[], ...handlers: readonly { readonly when: string; readonly then: readonly Stmt[]; }[]) => Stmt

Runs statements and, where one fails with a named condition (unique_violation), runs others instead.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

blockfunction
function block(spec: { readonly declare?: readonly (Var<unknown> & { readonly declared: Declared<LogicalType>; })[] | undefined; readonly body: readonly Stmt[]; readonly returns: Shape; }): Result<readonly Part[], readonly BlockError[]>

A block as the body of a routine.

ParameterWhat it is
specthe variables it declares and its statements, and whether the routine returns a value or nothing

Returns: the body (text and references), or what is wrong with the block

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

BlockErrortypeno test imports it
type BlockError =
  | { readonly kind: "undeclared"; readonly variable: string }
  | { readonly kind: "declared-twice"; readonly variable: string }
  | { readonly kind: "no-end"; readonly why: string }
  | { readonly kind: "return-value"; readonly why: string }
  | { readonly kind: "state"; readonly code: string };

What is wrong with a block.

doneconst
const done: () => Stmt

Returns from a procedure or a function that returns nothing.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

Declaredinterfaceno test imports it
interface Declared<L extends LogicalType = LogicalType>

A variable that a block declares.

MemberTypeWhat it is
namestring
typeL
initial?Expr<Of<L>>
exprfunction
function expr<T = unknown>(strings: TemplateStringsArray, ...holes: readonly Hole[]): Expr<T>

An expression from text and values: a variable, a reference, another expression or a literal (written as SQL writes it).

expr`${column("invoice", "total")} > ${val(100)}`;

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

Exprinterface
interface Expr<T = unknown>

An expression of a type T: parts of a body. The type is the compiler's; it is not in the value.

MemberTypeWhat it is
partsreadonly Part[]
__t?T

Imported by 20 tests of the run (20 passed, 0 failed): the library's results

forEachconst
const forEach: (record: string, query: Expr<unknown>, body: (row: (table: string, column: string) => Expr<unknown>) => readonly Stmt[]) => Stmt

Runs statements for each row of a query; the row's columns of a table are row(table, column).

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

foundconst
const found: Expr<boolean>

Whether the last statement touched a row.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

loopconst
const loop: (condition: Expr<boolean>, body: readonly Stmt[]) => Stmt

Repeats statements while a condition holds.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

noticeconst
const notice: (message: string, ...args: readonly Expr<unknown>[]) => Stmt

Says something to whoever runs the routine and goes on.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

Oftypeno test imports it
type Of<L extends LogicalType> = L extends { readonly kind: "text" | "date" | "instant" | "decimal" | "identifier" | "uuid" }
  ? string
  : L extends { readonly kind: "integer" }
    ? number
    : L extends { readonly kind: "boolean" }
      ? boolean
      : unknown;

The TypeScript type of the values of a logical type.

Parttypeno test imports it
type Part = string | Ref;

A piece of a body: text, or a reference the case style writes.

raiseconst
const raise: (message: string, ...args: readonly Expr<unknown>[]) => Stmt

Stops with an error; % in the message is replaced by the arguments, in order; code is the SQLSTATE (P0001 where none is given).

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

raiseCodeconst
const raiseCode: (code: string, message: string, ...args: readonly Expr<unknown>[]) => Stmt

Stops with an error and a SQLSTATE a handler can name.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

refconstno test imports it
const ref: <T = unknown>(r: Ref) => Expr<T>

A reference as an expression.

retconst
const ret: (value: Expr<unknown>) => Stmt

Returns the value of the function.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

retQueryconst
const retQuery: (query: Expr<unknown>) => Stmt

Returns the rows of a query as the rows of the function.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

runconst
const run: (statement: Expr<unknown>) => Stmt

Runs a statement for its effect.

Imported by 20 tests of the run (20 passed, 0 failed): the library's results

setconst
const set: <T>(target: Var<T>, value: Expr<NoInfer<T>>) => Stmt

Assigns a variable a value of its type.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

Shapetypeno test imports it
type Shape = "value" | "none";

What a block is checked against.

Stmttypeno test imports it
type Stmt = | { readonly kind: "assign"; readonly target: string; readonly value: Expr } | { readonly kind: "run"; r… (888 characters)
type Stmt =
  | { readonly kind: "assign"; readonly target: string; readonly value: Expr }
  | { readonly kind: "run"; readonly statement: Expr }
  | { readonly kind: "if"; readonly condition: Expr; readonly then: readonly Stmt[]; readonly otherwise: readonly Stmt[] }
  | { readonly kind: "while"; readonly condition: Expr; readonly body: readonly Stmt[] }
  | { readonly kind: "for"; readonly record: string; readonly query: Expr; readonly body: readonly Stmt[] }
  | { readonly kind: "raise"; readonly level: "exception" | "notice"; readonly message: string; readonly args: readonly Expr[]; readonly code?: string }
  | { readonly kind: "return"; readonly value?: Expr }
  | { readonly kind: "return-query"; readonly query: Expr }
  | { readonly kind: "attempt"; readonly body: readonly Stmt[]; readonly handlers: readonly { readonly when: string; readonly then: readonly Stmt[] }[] };

A statement of a block.

valconst
const val: <T extends string | number | boolean | null>(v: T) => Expr<T>

A literal of a type: a number, a boolean, text or null, written as SQL writes it.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

variableconst
const variable: <L extends LogicalType>(name: string, type: L, initial?: Expr<Of<L>> | undefined) => Var<Of<L>> & { readonly declared: Declared<L>; }

A variable, to be declared in a block and used in its statements.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

Varinterfaceno test imports it
interface Var<T = unknown> extends Expr<T>

A variable of a block.

MemberTypeWhat it is
variablestring
typeLogicalType
partsreadonly Part[]
__t?T
whenconst
const when: (condition: Expr<boolean>, then: readonly Stmt[], otherwise?: readonly Stmt[]) => Stmt

Does one thing where a condition holds and, where given, another where it does not.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

columnOfconst
const columnOf: <T = unknown>(table: string, name: string) => Expr<T>

A column of a table, qualified.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

paramOfconst
const paramOf: <T = unknown>(name: string) => Expr<T>

A parameter of the routine.

Imported by 2 tests of the run (2 passed, 0 failed): the library's results

tableOfconst
const tableOf: (name: string) => Expr<unknown>

A table.

Imported by 30 tests of the run (30 passed, 0 failed): the library's results

Titles, headings, exports, tables, columns and requirements. Esc closes.