CSE Roadmap

Common tier · about 3 hours · 5 parts

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();.