Saved functions

Save a formula with a name, and use it in any query like a built-in function.

• Category: Saved functions
• Keywords: function, user-defined function, UDF, formula, RETURNS, OR REPLACE, FUNCTIONS, CREATE OR REPLACE FUNCTION, calls itself, built-in function, nested too deeply
• See also: Stored procedures, The procedure language, Views, Math functions, 13. Functions, procedures and triggers

A saved function is a formula with a name. Its body is one expression (one formula, like (close - open) / open * 100). You write the formula once. Then every query can use it, like round or concat.

• KDB keeps the function in the database. It is still there after a restart and in a backup.
• A call costs nothing extra: when the query starts, KDB puts the body in the place of the call.
• Only one expression. For many steps, variables or loops, use a stored procedure.

Example: The change of each share today, in percent (sample data: sample)

CREATE FUNCTION change_pct(price DOUBLE, prev DOUBLE) RETURNS DOUBLE
AS ((price - prev) / prev * 100);

SELECT symbol, open, close, round(change_pct(close, open), 2) AS change
FROM ochl_day
WHERE time IN '2026-09-10'
ORDER BY change DESC;

Result:

CREATE FUNCTION change_pct(price DOUBLE, prev DOUBLE) RETURNS DOUBLE AS ((price - prev) / prev * 100

function 'change_pct' created

SELECT symbol, open, close, round(change_pct(close, open), 2) AS change FROM ochl_day WHERE time IN

(3 rows)

CREATE FUNCTION

Syntax:

CREATE [OR REPLACE] FUNCTION name(parameter TYPE, ...) [RETURNS TYPE] AS (expression)
CREATE [OR REPLACE] FUNCTION name() [RETURNS TYPE] AS (expression)

Saves a new function. OR REPLACE changes a function that already exists.

• Returns: the message function 'name' created (or replaced).
• Notes:
  • Each argument is changed to the type of its parameter, like CAST. So change_pct('5', '4') works: the text becomes a DOUBLE.
  • RETURNS DECIMAL(2) fails when the value has more than 2 decimals, for example 112.9887. Round first: AS (round(amount * 0.13, 2)).
  • Without OR REPLACE, a name that is already used gives an error.
  • KDB checks the body when you create the function: unknown functions, wrong argument counts and a function that calls itself are refused at once. The rules list everything a body cannot hold.
  • There are no default values for parameters, and no two functions with the same name and other parameters.

Example: VAT of 13% on an amount of money, exact to the cent

CREATE FUNCTION vat(amount DECIMAL(2)) RETURNS DECIMAL(2) AS (round(amount * 0.13, 2));

SELECT vat(1000) AS on_1000, vat(99.99) AS on_99_99, 99.99 + vat(99.99) AS total;

Result:

CREATE FUNCTION vat(amount DECIMAL(2)) RETURNS DECIMAL(2) AS (round(amount * 0.13, 2))

function 'vat' created

SELECT vat(1000) AS on_1000, vat(99.99) AS on_99_99, 99.99 + vat(99.99) AS total

(1 row)

Example: One function uses another: up, down or flat days (sample data: sample)

CREATE FUNCTION change_pct(price DOUBLE, prev DOUBLE) RETURNS DOUBLE
AS ((price - prev) / prev * 100);

CREATE FUNCTION move(price DOUBLE, prev DOUBLE) RETURNS STRING
AS (CASE WHEN change_pct(price, prev) > 0.5 THEN 'up'
         WHEN change_pct(price, prev) < -0.5 THEN 'down'
         ELSE 'flat' END);

SELECT move(close, open) AS move, count(*) AS days
FROM ochl_day
WHERE symbol = 'CITY'
GROUP BY move(close, open)
ORDER BY move;

Result:

CREATE FUNCTION change_pct(price DOUBLE, prev DOUBLE) RETURNS DOUBLE AS ((price - prev) / prev * 100

function 'change_pct' created

CREATE FUNCTION move(price DOUBLE, prev DOUBLE) RETURNS STRING AS (CASE WHEN change_pct(price, prev)

function 'move' created

SELECT move(close, open) AS move, count(*) AS days FROM ochl_day WHERE symbol = 'CITY' GROUP BY move

(3 rows)

Example: Change a function with OR REPLACE

CREATE FUNCTION fee(amount DOUBLE) RETURNS DOUBLE AS (amount * 0.004);
SELECT fee(100000) AS old_fee;

CREATE OR REPLACE FUNCTION fee(amount DOUBLE) RETURNS DOUBLE AS (greatest(amount * 0.0036, 10));
SELECT fee(100000) AS new_fee, fee(1000) AS small_trade;

Result:

CREATE FUNCTION fee(amount DOUBLE) RETURNS DOUBLE AS (amount * 0.004)

function 'fee' created

SELECT fee(100000) AS old_fee

(1 row)

CREATE OR REPLACE FUNCTION fee(amount DOUBLE) RETURNS DOUBLE AS (greatest(amount * 0.0036, 10))

function 'fee' replaced

SELECT fee(100000) AS new_fee, fee(1000) AS small_trade

(1 row)

Example: The name is already used (shows an error)

CREATE FUNCTION fee(amount DOUBLE) AS (amount * 0.004);
CREATE FUNCTION fee(amount DOUBLE) AS (amount * 0.0036);

Result:

function 'fee' created

Error: function 'fee' already exists; use CREATE OR REPLACE FUNCTION to change it

Example: RETURNS DECIMAL(2) with too many decimals (shows an error)

CREATE FUNCTION with_vat(amount DOUBLE) RETURNS DECIMAL(2) AS (amount * 1.13);
SELECT with_vat(99.99);

Result:

function 'with_vat' created

Error: line 2, column 8: cannot convert '112.98869999999998' to DECIMAL(2): 112.98869999999998 has more than 2 decimal places

Using a saved function

Syntax:

SELECT name(argument, ...) ... FROM ... WHERE name(...) ... GROUP BY name(...) ORDER BY name(...)

Call a saved function like a built-in one: its name and the arguments in brackets. It works in SELECT, WHERE, GROUP BY, ORDER BY, in views, in procedures and in triggers.

• Returns: the value of the body, for each row.
• Notes:
  • The number of arguments must match the parameters.
  • Aggregates go around the call: sum(change_pct(close, open)) works. They cannot go inside the body.
  • A saved function cannot be used in a computed column (AS (...) in CREATE TABLE), and not with DISTINCT or OVER.
  • Views and procedures always use the newest body: after CREATE OR REPLACE FUNCTION, a view that uses it gives the new result.
  • The column name of an unnamed call is the call itself, like change_pct(1, 2). Use AS to give it a better name.

Example: Big trades only: a function in WHERE (sample data: sample)

CREATE FUNCTION trade_value(quantity LONG, rate DOUBLE) RETURNS DOUBLE AS (quantity * rate);

SELECT transaction_id, symbol, quantity, rate, trade_value(quantity, rate) AS value
FROM trade_log
WHERE trade_value(quantity, rate) >= 250000
ORDER BY value DESC;

Result:

CREATE FUNCTION trade_value(quantity LONG, rate DOUBLE) RETURNS DOUBLE AS (quantity * rate)

function 'trade_value' created

SELECT transaction_id, symbol, quantity, rate, trade_value(quantity, rate) AS value FROM trade_log W

(9 rows)

Example: A view uses the newest body of the function

CREATE FUNCTION fee(amount DOUBLE) RETURNS DOUBLE AS (amount * 0.004);
CREATE VIEW fee_on_lakh AS SELECT fee(100000) AS fee;
SELECT * FROM fee_on_lakh;

CREATE OR REPLACE FUNCTION fee(amount DOUBLE) RETURNS DOUBLE AS (amount * 0.0036);
SELECT * FROM fee_on_lakh;

Result:

CREATE FUNCTION fee(amount DOUBLE) RETURNS DOUBLE AS (amount * 0.004)

function 'fee' created

CREATE VIEW fee_on_lakh AS SELECT fee(100000) AS fee

view 'fee_on_lakh' created

SELECT * FROM fee_on_lakh

(1 row)

CREATE OR REPLACE FUNCTION fee(amount DOUBLE) RETURNS DOUBLE AS (amount * 0.0036)

function 'fee' replaced

SELECT * FROM fee_on_lakh

(1 row)

Example: Wrong number of arguments (shows an error)

CREATE FUNCTION change_pct(price DOUBLE, prev DOUBLE) RETURNS DOUBLE AS ((price - prev) / prev * 100);
SELECT change_pct(110);

Result:

function 'change_pct' created

Error: line 2, column 8: function 'change_pct' takes 2 argument(s) (price DOUBLE, prev DOUBLE), got 1

Example: Not in a computed column (shows an error)

CREATE FUNCTION trade_value(quantity LONG, rate DOUBLE) RETURNS DOUBLE AS (quantity * rate);
CREATE TABLE trades (quantity LONG, rate DOUBLE, value DOUBLE AS (trade_value(quantity, rate)));

Result:

function 'trade_value' created

Error: computed column 'value' cannot use the saved function 'trade_value'; computed columns can use built-in functions only

Rules for the body

Syntax:

AS (expression using only the parameters)

The body is checked when you create the function. These things are refused:

• Notes: DROP FUNCTION does not check who uses a function. A function, view or procedure that uses a dropped function fails when it runs, with unknown function.

Example: A built-in name (shows an error)

CREATE FUNCTION pct_change(price DOUBLE, prev DOUBLE) AS ((price - prev) / prev * 100);

Result:

Error: 'pct_change' is a built-in function; choose another name for the function

Example: A column that is not a parameter (shows an error)

CREATE FUNCTION day_range(high DOUBLE) AS (high - low);

Result:

Error: the body of function 'day_range' uses low, which is not one of its parameters (high DOUBLE); a function can only use its parameters

Example: An aggregate inside the body (shows an error)

CREATE FUNCTION total(x DOUBLE) AS (sum(x));

Result:

Error: the body of function 'total' cannot use 'sum': aggregate and window functions work on many rows (call them around the function instead)

Example: A subquery inside the body (shows an error)

CREATE FUNCTION last_close(s STRING) AS ((SELECT last(close) FROM ochl_day WHERE symbol = s));

Result:

Error: the body of function 'last_close' cannot use a subquery

Example: Two functions that call each other (shows an error)

CREATE FUNCTION a(x DOUBLE) AS (x + 1);
CREATE FUNCTION b(x DOUBLE) AS (a(x) * 2);
CREATE OR REPLACE FUNCTION a(x DOUBLE) AS (b(x));

Result:

CREATE FUNCTION a(x DOUBLE) AS (x + 1)

function 'a' created

CREATE FUNCTION b(x DOUBLE) AS (a(x) * 2)

function 'b' created

Error: in function 'a': in function 'b': function 'a' calls itself (a -> b -> a); a saved function cannot call itself

DROP FUNCTION

Syntax:

DROP FUNCTION [IF EXISTS] name

Removes a saved function.

• Returns: function 'name' dropped. With IF EXISTS, a name that does not exist gives the message function 'name' does not exist and no error.
• Notes: KDB does not check who uses the function. Other functions, views, procedures and triggers that use it fail later, when they run.

Example

CREATE FUNCTION fee(amount DOUBLE) RETURNS DOUBLE AS (amount * 0.004);
DROP FUNCTION fee;
DROP FUNCTION IF EXISTS fee;

Result:

CREATE FUNCTION fee(amount DOUBLE) RETURNS DOUBLE AS (amount * 0.004)

function 'fee' created

DROP FUNCTION fee

function 'fee' dropped

DROP FUNCTION IF EXISTS fee

function 'fee' does not exist

Example: A function that used the dropped one fails when it runs (shows an error)

CREATE FUNCTION a(x DOUBLE) AS (x + 1);
CREATE FUNCTION b(x DOUBLE) AS (a(x) * 2);
DROP FUNCTION a;
SELECT b(1);

Result:

CREATE FUNCTION a(x DOUBLE) AS (x + 1)

function 'a' created

CREATE FUNCTION b(x DOUBLE) AS (a(x) * 2)

function 'b' created

DROP FUNCTION a

function 'a' dropped

Error: line 4, column 8: in function 'b': unknown function 'a'

Example: Without IF EXISTS, a missing name is an error (shows an error)

DROP FUNCTION no_such_function;

Result:

Error: function 'no_such_function' does not exist

SHOW FUNCTIONS

Syntax:

SHOW FUNCTIONS

Lists the saved functions of the database, in name order.

• Notes: built-in functions are not listed. The text table of kdb sql cuts long text after 48 characters; use --format csv or --format json to see the whole definition.

Example

CREATE FUNCTION change_pct(price DOUBLE, prev DOUBLE) RETURNS DOUBLE AS ((price - prev) / prev * 100);
CREATE FUNCTION vat(amount DECIMAL(2)) RETURNS DECIMAL(2) AS (round(amount * 0.13, 2));
CREATE FUNCTION greeting(name STRING) AS (concat('Namaste, ', name));
SHOW FUNCTIONS;

Result:

CREATE FUNCTION change_pct(price DOUBLE, prev DOUBLE) RETURNS DOUBLE AS ((price - prev) / prev * 100

function 'change_pct' created

CREATE FUNCTION vat(amount DECIMAL(2)) RETURNS DECIMAL(2) AS (round(amount * 0.13, 2))

function 'vat' created

CREATE FUNCTION greeting(name STRING) AS (concat('Namaste, ', name))

function 'greeting' created

SHOW FUNCTIONS

(3 rows)