CREATE FUNCTION in SQL

A saved expression the planner can call anywhere a value fits.

-- A function is a saved expression the planner can call anywhere.
CREATE FUNCTION line_total(quantity INTEGER, price NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
IMMUTABLE
RETURN quantity * price;

SELECT
    oi.sku,
    line_total(oi.quantity, p.price) AS owed
FROM order_items AS oi
JOIN products AS p ON p.sku = oi.sku
WHERE oi.order_id = 103;

How it works

1. CREATE FUNCTION line_total(...) declares typed parameters and a return type.
2. LANGUAGE SQL with a bare RETURN is the modern standard body form.
3. The query then calls it per row, exactly like a built-in.

Keywords and builtins used here

• AS
• CREATE
• FROM
• FUNCTION
• IMMUTABLE
• INTEGER
• JOIN
• LANGUAGE
• NUMERIC
• ON
• RETURN
• RETURNS
• SELECT
• SQL
• WHERE

The run, in numbers

Lines

13

Characters to type

332

Tokens

61

Three-star pace

70 tpm

At the three-star pace of 70 tokens a minute, this run takes about 52 seconds.

Fastest run

1. 🇺🇸 brendancol43 tpm

Type this snippet

Step 1 of 4 in Functions, step 1 of 23 in PostgreSQL Stored Procedures.

Next →