Part 2 of 5 · 1 min read
Stored functions and procedures
Logic that runs inside the database, callable by name.
Think of a form with a button labelled "calculate total." It runs a fixed calculation right there, instead of mailing the numbers elsewhere and waiting for an answer.
In plain words: a stored function returns a value and can be used inside a query, such as in a SELECT list. A stored procedure performs an action, such as a sequence of inserts and updates, and is called on its own, not embedded inside a query's expression.
A worked example. A function computing a book's price after its discount, usable directly in a SELECT.
The key lines.
CREATE FUNCTION discounted_price(book_price NUMERIC, discount NUMERIC)
RETURNS NUMERIC AS $$
SELECT book_price * (1 - discount / 100);
$$ LANGUAGE SQL;
SELECT title, discounted_price(price, discount) FROM books;
Stored function, stored procedure
A stored function returns a value and can be used inside a query expression. A stored procedure performs an action and is called directly, not embedded inside a query.
Example: discounted_price(...) is a function, usable inside a SELECT list; a procedure that archives all orders older than a year would instead be called on its own, such as CALL archive_old_orders();.