# Functions

`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.

```ts title="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`, // [!code focus]
});

export default schema({
  tables: [notes],
  functions: [answer], // [!code focus]
});
```

#### 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.

```ts title="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`, // [!code focus]
});

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](/docs/define/schema/casing) `"snake"`, `updatedAt` is `updated_at`.

```ts title="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" }], // [!code focus]
});

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

<Callout type="error" title="OKM1824">
plpgsql without `dependsOn` is `Function touch_notes is plpgsql and has no dependsOn. Postgres does not record dependencies inside the body.`
</Callout>

<Callout type="error" title="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.`
</Callout>

<Callout type="error" title="OKM1023">
The same name and the same argument types twice is `Duplicate identity for function touch_notes.`
</Callout>

### arguments

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

```ts title="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" }], // [!code focus]
  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"`.

```ts title="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", // [!code focus]
  body: sql`select 1`,
});

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

<Callout type="error" title="OKM1020">
Another value is `Function answer volatility literal is not supported.`
</Callout>

### security

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

```ts title="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", // [!code focus]
  searchPath: "public", // [!code focus]
  body: sql`select 1`,
});

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

<Callout type="error" title="OKM1823">
Definer with no `searchPath`, or one that is only spaces, is `Function answer is security definer and has no search_path.`
</Callout>

<Callout type="error" title="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.`
</Callout>

### install schema

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

```ts title="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", // [!code focus]
  body: sql`select 1`,
});

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

### together

A definer, arguments, a column dependency, and a [trigger](/docs/define/schema/triggers) that calls it. [Casing](/docs/define/schema/casing) `"snake"` makes the column `updated_at`.

```ts title="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], // [!code focus]
  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$`.

```ts title="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`, // [!code focus]
});

export default schema({
  tables: [notes],
  functions: [answer], // [!code focus]
});
```

#### 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"()`.

```ts title="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" }], // [!code focus]
  returns: "text",
  language: "sql",
  volatility: "immutable", // [!code focus]
  body: sql`select lower(title)`,
});

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

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,
    }),
  ],
});
```

<Callout type="error" title="OKM1823">
Definer with no `searchPath` is `Function touch_notes is security definer and has no search_path.`
</Callout>