You call built-in functions such as ROUND, UPPER and COALESCE in your queries. With CREATE FUNCTION you add your own.

A function that calculates

The shop gives members 10 % off. Write the rule once:

CREATE FUNCTION member_price(p numeric)
RETURNS numeric
LANGUAGE sql
AS $$
  SELECT ROUND(p * 0.9, 2)
$$;

• A parameter is a name and a type. Inside the body, p is the value of the call.
• The body is one SELECT. Its value is the result of the function.
• $$ starts and ends the body. The body is text between the two marks. It can contain single quotes.

Call the function anywhere a value can go:

SELECT name, price, member_price(price) AS member
FROM products
WHERE member_price(price) < 20;

A function that reads a table

The body can query tables. This one counts a customer's orders:

CREATE FUNCTION customer_orders(c integer)
RETURNS bigint
LANGUAGE sql
AS $$
  SELECT COUNT(*) FROM orders WHERE customer_id = c
$$;

SELECT name, customer_orders(id) FROM customers;

COUNT returns a bigint, so the function returns bigint too.

Change or remove a function

• CREATE OR REPLACE FUNCTION replaces the body. Keep the same parameter types and result type.
• DROP FUNCTION member_price; removes it.