A LANGUAGE sql function holds one SELECT. For steps, variables and decisions, write the body in .
sql
CREATE FUNCTION order_size(o integer)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
n integer;
BEGIN
SELECT SUM(quantity) INTO n FROM order_items WHERE order_id = o;
RAISE NOTICE 'order % has % items', o, n;
IF n >= 5 THEN
RETURN 'large';
ELSIF n >= 2 THEN
RETURN 'medium';
ELSE
RETURN 'small';
END IF;
END;
$$;DECLAREn integer;
variables, each with a typeBEGIN … END;SELECT … INTO, RAISE NOTICE, IF, RETURN
statements, each ends with ;The statements
| Statement | What it does |
|---|---|
SELECT … INTO n FROM … | runs the query and puts the value in n |
n := n + 1; | gives a variable a new value |
IF … THEN … ELSIF … ELSE … END IF; | runs one branch: the first condition that is true |
RETURN value; | ends the function with this result |
RAISE NOTICE 'text %', value; | sends a message; each % takes the next value |
Call it like any function. The notice shows under the statement in the Result tab:
sql
SELECT order_size(105);text
NOTICE: order 105 has 5 items
largeUse notices to follow what a function does while you write it.