Functions | OKModel

functions is a list of fn() from okmodel/fn. The catalog record is built when the catalog is first read. schema() does not run the body. Overloads differ by argument types. The same name with the same argument types is one function.

returns, language, and body are required. language is "sql" or "plpgsql". Volatility defaults to "volatile". Security defaults to "invoker". The install schema defaults to public.

The value fn() returns is callable. slugify(ident("title")) is a where operand. slugify(ident("title")).as("slug") is a select entry. A numeric return is a number. A boolean return is read as text, as is any other non-numeric return. A string, number, boolean, or null is a parameter. ident names a field. orderBy does not accept the call.

sql

"sql" stores the body as written. dependsOn is optional.

sql.ts

import { fn } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const answer = fn("answer", {
  returns: "integer",
  language: "sql",
  body: sql`select 1`, 
});

export default schema({
  tables: [notes],
  functions: [answer], 
});

atomic

A body that begins with BEGIN ATOMIC is marked atomic. Its edges are read from pg_depend. It does not need dependsOn. The catalog keeps the text you wrote. okm migrate check and okm check reprint that body on the connected server before they compare, so a qualifier Postgres adds, such as by_id.n for an argument used beside a table, is not a difference (D273). Two overloads of one name are matched by that name and the identity arguments, so each keeps its own reprinted body.

atomic.ts

import { fn } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const answer = fn("answer", {
  returns: "integer",
  language: "sql",
  body: sql`begin atomic select 1`, 
});

export default schema({
  tables: [notes],
  functions: [answer],
});

plpgsql

"plpgsql" needs dependsOn. Postgres does not record dependencies inside that body. A table is an object dependency. { table, column } is a column dependency. column is the SQL name. With casing "snake", updatedAt is updated_at.

plpgsql.ts

import { fn } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
  updatedAt: t.timestamptz().defaultSql("now()"),
});

const touch = fn("touch_notes", {
  returns: "trigger",
  language: "plpgsql",
  body: sql`begin new."updatedAt" = now(); return new; end`,
  dependsOn: [{ table: notes, column: "updatedAt" }], 
});

export default schema({
  tables: [notes],
  functions: [touch],
});

OKM1824

plpgsql without dependsOn is Function touch_notes is plpgsql and has no dependsOn. Postgres does not record dependencies inside the body.

OKM1020

A missing column is dependsOn names column updated_at, which notes does not have. A table that is not in this schema is Table notes is not in the catalog. An empty body is Function touch_notes needs a body. BEGIN ATOMIC with plpgsql is Function touch_notes BEGIN ATOMIC needs language sql. Another language is Function touch_notes language must be sql or plpgsql.

OKM1023

The same name and the same argument types twice is Duplicate identity for function touch_notes.

arguments

Each argument has a name and a type. Names live in the definition. Argument types are the identity.

arguments.ts

import { fn } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const lengthOf = fn("length_of", {
  arguments: [{ name: "title", type: "text" }], 
  returns: "integer",
  language: "sql",
  body: sql`select char_length(title)`,
});

export default schema({
  tables: [notes],
  functions: [lengthOf],
});

volatility

"volatile", "stable", or "immutable". Omitted is "volatile".

volatility.ts

import { fn } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const answer = fn("answer", {
  returns: "integer",
  language: "sql",
  volatility: "immutable", 
  body: sql`select 1`,
});

export default schema({
  tables: [notes],
  functions: [answer],
});

OKM1020

Another value is Function answer volatility literal is not supported.

security

"invoker" or "definer". Omitted is "invoker". "definer" needs searchPath. Each schema in that list, split on commas, is an identifier.

security.ts

import { fn } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const answer = fn("answer", {
  returns: "integer",
  language: "sql",
  security: "definer", 
  searchPath: "public", 
  body: sql`select 1`,
});

export default schema({
  tables: [notes],
  functions: [answer],
});

OKM1823

Definer with no searchPath, or one that is only spaces, is Function answer is security definer and has no search_path.

OKM1020

An empty searchPath on an invoker is Function answer search_path is empty. Another security value is Function answer security owner is not supported.

install schema

schema on fn() is the install schema. The default is public.

install.ts

import { fn } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const answer = fn("answer", {
  returns: "integer",
  language: "sql",
  schema: "app", 
  body: sql`select 1`,
});

export default schema({
  tables: [notes],
  functions: [answer],
});

together

A definer, arguments, a column dependency, and a trigger that calls it. Casing "snake" makes the column updated_at.

together.ts

import { fn, trigger } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
  updatedAt: t.timestamptz().defaultSql("now()"),
});

const touch = fn("touch_notes", {
  arguments: [{ name: "extra", type: "text" }],
  returns: "trigger",
  language: "plpgsql",
  security: "definer",
  searchPath: "public",
  body: sql`begin new.updated_at = now(); return new; end`,
  dependsOn: [{ table: notes, column: "updated_at" }],
});

export default schema({
  tables: [notes],
  casing: "snake",
  functions: [touch], 
  triggers: [
    trigger("notes_touch", {
      on: notes,
      timing: "before",
      events: ["update"],
      level: "row",
      calls: touch,
    }),
  ],
});

Create your own

There is no built-in function. fn() from okmodel/fn is the declaration. The sections above are its options.

Simple

One SQL function, no arguments. okm generate writes it after the table: create function "public"."answer"() returns integer language sql volatile security invoker as $okm$select 1$okm$.

own.ts

import { fn } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
});

const answer = fn("answer", {
  returns: "integer",
  language: "sql",
  body: sql`select 1`, 
});

export default schema({
  tables: [notes],
  functions: [answer], 
});

Advanced

slugify is immutable and takes one argument. touch_notes is plpgsql, so it lists the column it writes, and it is a definer, so it sets searchPath. The trigger is created after both functions. okm generate writes the table, then slugify, then touch_notes, then create trigger "notes_touch" before update of "title" on "public"."notes" for each row when (old.title is distinct from new.title) execute function "public"."touch_notes"().

own-advanced.ts

import { fn, trigger } from "okmodel/fn";
import { schema, sql, table, t } from "okmodel/pg";

const notes = table("notes", {
  id: t.identity(),
  title: t.text(),
  updatedAt: t.timestamptz().defaultSql("now()"),
});

const slugify = fn("slugify", {
  arguments: [{ name: "title", type: "text" }], 
  returns: "text",
  language: "sql",
  volatility: "immutable", 
  body: sql`select lower(title)`,
});

const touch = fn("touch_notes", {
  returns: "trigger",
  language: "plpgsql",
  security: "definer", 
  searchPath: "public", 
  body: sql`begin new.updated_at = now(); return new; end`,
  dependsOn: [{ table: notes, column: "updated_at" }], 
});

export default schema({
  tables: [notes],
  casing: "snake",
  functions: [slugify, touch],
  triggers: [
    trigger("notes_touch", {
      on: notes,
      timing: "before",
      events: ["update"],
      level: "row",
      updateOf: ["title"],
      when: sql`old.title is distinct from new.title`,
      calls: touch,
    }),
  ],
});

OKM1823

Definer with no searchPath is Function touch_notes is security definer and has no search_path.

On this page