Everything you've written so far is a query: you send it, it runs and it's forgotten. This lesson is about the opposite — named code that lives inside the database and is invoked as if it were part of the language. This is where the order confirmation you wrote by hand in 09-03 will end up living, with its BEGIN, its four statements and its COMMIT, and which until now depended on whoever ran it not skipping a step.

You'll see the real difference between a function and a procedure in PostgreSQL 11+, just enough PL/pgSQL to write something useful without turning this into a programming course, and three GreenStore examples in order of difficulty ending in sp_confirm_order. And, above all, the honest discussion: what a team gains by putting logic in the database, what it loses, and where the line is. Because this is the tool in the module that's easiest to misuse.

1. Function versus procedure
2. CREATE FUNCTION with LANGUAGE sql
3. PL/pgSQL in one reference table
4. Parameters, return types, overloading and dropping
5. fn_order_total: a scalar function
6. fn_sales_by_category: a function that returns a table
7. sp_confirm_order: 09-03's procedure
8. Volatility and SECURITY DEFINER
9. The honest discussion: what to put inside and what not
10. Common Mistakes and Tips
11. Exercises
12. Conclusion

Up to PostgreSQL 10 there were only functions, and they were used for everything. From 11 on there are procedures too, and the confusion between the two is constant. The table that settles it:

The dividing line is transaction control. A function runs inside the transaction of whoever calls it; if that transaction is rolled back, everything the function did is rolled back with it. A procedure called with CALL outside an explicit transaction can commit on its own, which lets you write a batch process that commits every thousand rows without keeping one giant transaction open (09-01).

The practical rule: if it returns a value, function; if it does a job, procedure.

The simplest form needs no procedural language at all: it's a query with a name and parameters.

433.70 + 156.48 + 137.77 = €727.95: the per-country figures you already computed in 07-04, now encapsulated. Four syntax details that will recur throughout the lesson:

• $$ ... $$ delimits the body. It's 01-03's dollar quoting: inside it you can write single quotes without escaping them. If the body contains $$, use a tag: $fn$ ... $fn$.
• Parameters are named with a prefix (p_country) by convention, so they don't clash with column names. If a parameter were called country, PostgreSQL wouldn't know which one you meant in the WHERE.
• RETURNS NUMERIC declares the type. With LANGUAGE sql, the result is that of the last statement.
• STABLE is the volatility (section 8).

(Every function and procedure in this lesson is an example: none of them is part of the canonical GreenStore schema from 01-06.)

LANGUAGE sql can't decide or repeat. That's what PL/pgSQL is for, the procedural language PostgreSQL ships with. Here's the essential —just enough to write something useful— and you won't need more for this lesson:

Two warnings that save hours. The first: BEGIN and END in PL/pgSQL delimit a block of code, not a transaction; the word is the same and it confuses everybody. The second: a block with an EXCEPTION internally creates a SAVEPOINT (09-03) and has a cost, so don't wrap a million-iteration loop in an EXCEPTION unless you need to.

RETURNS TABLE(...) versus RETURNS SETOF record: the first declares the column names and types inside the function, and the caller simply writes SELECT * FROM fn(...). The second forces you to describe the structure on every call —SELECT * FROM fn(...) AS t(id INT, name TEXT)— which is awkward and fragile. Use RETURNS TABLE unless the shape of the result genuinely depends on the arguments.

On overloading, a warning: it's convenient but it turns treacherous with types. fn(1) and fn(1.0) can resolve to different functions, and DROP FUNCTION fn without a signature gives function name "fn" is not unique. Prefer different names over overloading, unless the overload is obvious.

The first example, and the one you'll use most: an order's total, with the canonical amount expression.

And here's the charm of it: it's used like any other column, inside any query.

The four totals match those in 07-04: order 12 is the highest order value (€66.90) and order 20 the lowest (€22.60).

And here's the danger, stated up front: SELECT id, fn_order_total(id) FROM orders runs the function once per row — 20 independent queries against order_lines. It's exactly 07-02's correlated subquery, now hidden behind a pretty name. For a 20-order report it doesn't matter; for a two-million-order one, it's the difference between 200 ms and half an hour. A scalar function inside a bulk SELECT is a JOIN in disguise, and it's almost always better to write the JOIN (or use 10-01's view).

With RETURNS TABLE, a function behaves like a parameterized table — something halfway between a view and a standalone query, and something views can't do because a view takes no parameters.

They add up to €603.52, 2025's revenue. And with SELECT * FROM fn_sales_by_category(2026) only four categories come out —Drinks €48.73, Food €40.60, Natural cosmetics €28.10 and Personal hygiene €7.00— adding up to 2026's €124.43: in the first two months of the year nothing from Sustainable home has been sold.

Like any table, it can be filtered and joined: SELECT * FROM fn_sales_by_category(2025) WHERE revenue > 100; returns three rows.

When a function and when a view (10-01): if the result doesn't depend on any parameter, a view. If it depends on an argument —the year, the country, a date range— a function returning a table. And if the parameter only serves to filter a column the view already exposes, a view plus a WHERE, which the planner optimizes better.

This is the destination 09-03 and 09-05 announced. Confirming an order is several operations that have to go together: create the header, insert the lines, deduct the stock checking it doesn't go negative and mark the order as paid. If something fails, nothing must be left half-done.

The call, with the happy case and the failure case:

And order 22 doesn't exist. That's the whole lesson of this section: the RAISE EXCEPTION aborted the transaction, and the header that had already been inserted was rolled back with it. 09-02's atomicity is guaranteed by construction, not by the caller's discipline. A CALL outside an explicit transaction runs in its own implicit transaction, so the procedure is atomic without writing a BEGIN or a COMMIT.

Compare it with what you had in 09-03: there, the application had to open the transaction, run the four statements in the right order, check the stock's UPDATE N and decide whether to commit. Four places to get it wrong, multiplied by every application that confirms orders. Here there's one.

(This procedure modifies the data. If you try it, reload 01-06's script afterwards to get back to the 20 rows in orders and the original stock levels.)

The explicit COMMIT has one clear case: the batch process. A procedure that recomputes a million rows and commits every thousand avoids keeping a transaction open for hours —with the cost in VACUUM and locks 09-01 explained:

It's exactly what a function can't do, and the reason procedures were added to the engine.

Every function declares —explicitly or implicitly— how much the planner can trust it:

VOLATILE being the default is the most expensive silent mistake in this lesson. A function that only reads and declares nothing is evaluated row by row and can't be indexed, so this is where 08-02's promise gets kept: an expression index like CREATE INDEX ix_prod_lower ON products (lower(name)); requires the function to be IMMUTABLE. If it isn't, PostgreSQL answers functions in index expression must be marked IMMUTABLE — because an index stores precomputed results and only makes sense if those results don't change.

Mark every read-only function STABLE and every pure-computation function IMMUTABLE. And don't lie to the planner: declaring something IMMUTABLE when it reads a table produces incorrect results that are hard to diagnose.

SECURITY DEFINER. By default a function runs with the permissions of whoever calls it (SECURITY INVOKER). With SECURITY DEFINER it runs with those of whoever created it, which lets you give controlled access to data the user can't read directly. It's powerful and it's an attack surface: badly written, with a manipulable search_path, it's a textbook privilege escalation. Permissions, roles and how to harden these functions are the subject of 11-03.

This is the most valuable part of the lesson, and where most tutorials go quiet.

And the criterion, which is what you should take away:

The balanced position, and the one most teams hold today: little code in the database, and only good code. Constraints and CHECKs always; views and computation functions, no problem; procedures for what genuinely demands atomicity or bulk processing. What doesn't work is either extreme: neither an anaemic application that just calls two hundred procedures, nor a database treated as a dumb store in which no rule can be guaranteed.

Dialect note: this is where the engines diverge most.

Engine	Language	Quirk
PostgreSQL	PL/pgSQL (and PL/Python, PL/Perl, PL/v8…)	CREATE FUNCTION / CREATE PROCEDURE; CALL; body between $$
MySQL 8	SQL/PSM	You need DELIMITER // before creating the procedure, because the internal ; would cut the statement short
SQL Server	T-SQL	CREATE PROCEDURE ... AS BEGIN ... END, EXEC, @variables, TRY...CATCH
Oracle	PL/SQL	PL/pgSQL's ancestor: they're very alike, but packages (PACKAGE) have no equivalent
SQLite	—	It has no stored procedures or functions. Only functions defined by the application when it opens the connection

• Calling a procedure with SELECT. It isn't an expression: CALL sp_confirm_order(...). And the other way round, CALL on a function errors out.
• Expecting a COMMIT inside a function to work. It can't: cannot commit while a subtransaction is active or invalid transaction termination. Only procedures, and only if the CALL isn't inside an explicit transaction.
• Leaving the volatility at its default. Everything is VOLATILE if you say nothing: it's re-evaluated row by row and is no use for an expression index (functions in index expression must be marked IMMUTABLE). Mark read-only things STABLE.
• Calling a scalar function over millions of rows. It's a correlated subquery in disguise: one execution per row. Rewrite it as a JOIN, a view or a table-returning function.
• Giving a parameter the same name as a column. The ambiguity resolves in favour of the parameter and the WHERE stops filtering. Always prefix with p_.
• Confusing PL/pgSQL's BEGIN with a transaction's. It delimits a block of code. The transaction is the caller's.
• Using EXCEPTION WHEN OTHERS THEN NULL. It silences every error, including the ones you should never have silenced. Catch specific exceptions and re-raise whatever you don't know how to handle.
• Writing ELSEIF or ELSE IF. In PL/pgSQL it's ELSIF.
• Tip: keep the code in the repository, in .sql files with CREATE OR REPLACE, and deploy it with your migrations. A function that only exists in production doesn't exist.
• Tip: start with LANGUAGE sql. If you don't need to decide or repeat, don't use PL/pgSQL: the pure-SQL version is shorter, faster and the planner can inline it into the query that calls it.
• Tip: RAISE NOTICE is your debugger. There isn't much else, and a RAISE NOTICE 'v_stock = %', v_stock; at the key points solves 90 % of problems.

Write fn_average_rating(p_product_id), returning a product's average rating rounded to two decimals, or NULL if it has no reviews. (1) Choose LANGUAGE sql or plpgsql and justify it. (2) Declare the right volatility. (3) Use it in a query listing the 20 products with their average, and check that the olive oil (product 1) comes out with 5.00, the rice with 4.50 and eleven products with NULL.

On sp_confirm_order, reason out your answers and then check them. (1) If the second product in the list is out of stock, does the first line stay inserted? And the order header? Why? (2) What would happen if you removed the FOR UPDATE from the SELECT on products and two customers confirmed the last unit of matcha at the same time? (3) Could you turn it into a function instead of a procedure? What would you lose?

The team is arguing about where to put four GreenStore rules. Decide, using section 9's criterion, whether they go in the database or in the application, and with exactly which tool:

1. "The sale price can never be negative."
2. "When an order is confirmed, a confirmation email must be sent to the customer."
3. "Every night the ranking of the month's best-selling products has to be recomputed."
4. "A customer can't have more than three pending orders at a time."

Solution 1

(First 4 of 20 rows; next come the detergent and the toothbrush with 4.00, the tomato and the bags with 3.00, the kombucha with 2.00 and eleven products with NULL.)

1. LANGUAGE sql: there's no decision and no loop, just a query. It's shorter and the planner can inline it into the calling query, which doesn't happen with plpgsql. 2. STABLE: it reads tables, so it can't be IMMUTABLE; and it doesn't write, so declaring it VOLATILE would waste optimizations. 3. AVG over an empty set returns NULL with no need for any IF — and here NULL is the right answer: "no opinions" isn't the same as "zero stars" (04-03).

Solution 2

1. Nothing is left: neither the line nor the header. The RAISE EXCEPTION aborts the whole transaction, and since the CALL ran in its own implicit transaction, everything done since the start of the procedure is undone — including the first line, which was already inserted, and the UPDATE that had already deducted its stock. It's 09-02's atomicity applied without anyone having to remember to write a ROLLBACK. What does get consumed is the value from the orders.id sequence, because of 09-05: nextval isn't transactional.

2. It would be 09-04's lost update, exactly. Without FOR UPDATE, both sessions would read stock = 1, both would pass the IF v_stock < quantity, and both would insert their line: two units would be sold out of one. The table's CHECK (stock >= 0) would save the day in this specific case —the second UPDATE would fail trying to leave the stock at −1— but that's luck, not design. With FOR UPDATE, the second session waits, rereads stock = 0 and raises its not-enough-stock exception, which is the right message for the customer.

3. Yes, and it would work just as well in this case, because all the logic fits in a single transaction: a CREATE FUNCTION ... RETURNS INTEGER returning the order id would do, run with a SELECT. What you'd lose is the ability to make intermediate COMMITs, irrelevant here but decisive if one day the procedure had to confirm a thousand orders in batches. You gain something in exchange: a function can be used inside a query. The honest choice is section 1's: this does a job, it doesn't compute a value, so procedure.

Solution 3

The fourth is the interesting one and has no single answer: the right question isn't "can it be done?" but "what happens if somebody bypasses it?". If the answer is "corrupt data", it goes in the database; if it's "an odd shopping experience", it goes in the application.

Code that lives in the database, with its two faces:

• Function versus procedure: a function returns a value, is called with SELECT, can be used inside a query and doesn't control transactions; a procedure is called with CALL, returns nothing (except through INOUT) and can COMMIT/ROLLBACK. If it returns a value, function; if it does a job, procedure.
• LANGUAGE sql is enough to encapsulate a query with parameters, and it's the first choice. PL/pgSQL adds DECLARE, IF/ELSIF, FOR ... IN SELECT, RETURN QUERY, RAISE NOTICE/EXCEPTION and the EXCEPTION WHEN block — with the warning that its BEGIN is not a transaction's.
• RETURNS TABLE(...) turns a function into a parameterized table, which is what a view can never be. If there are no parameters, view; if there are, function.
• The three examples: fn_order_total (scalar, with the order values 42.10, 64.88, 66.90 and €22.60), fn_sales_by_category (table, €603.52 in 2025 and €124.43 in 2026) and sp_confirm_order, where 09-03's logic finally lives: it inserts, deducts stock with FOR UPDATE, raises a RAISE EXCEPTION if there isn't enough and leaves the database untouched if anything fails.
• Volatility matters: VOLATILE is the default and means "re-evaluate me on every row"; IMMUTABLE is a requirement for an expression index (08-02) and STABLE is right for nearly every read-only function. SECURITY DEFINER gives power and opens an attack surface (11-03).
• And the honest discussion: one call instead of six journeys, logic next to the data, guaranteed atomicity and reuse, against difficult versioning, awkward testing, logic in two places, zero portability and scaling tied to the DB server. The sensible position is little code in the database, and only good code.

There's one question this scheme doesn't answer: what if the logic has to run without anyone calling it? A procedure has to be invoked, and all it takes is one application writing straight into the table to bypass the whole thing. In the next lesson, triggers, you'll see code that fires by itself when something happens: BEFORE or AFTER an INSERT, UPDATE or DELETE, with NEW and OLD, and with the ability to modify or cancel the operation in progress. With it you'll build the price-change audit trail, keep a denormalized total up to date, stop a customer from reviewing a product they haven't bought —the rule 09-02 left beyond a CHECK's reach— and deduct stock automatically. And you'll see its dangers in the same detail, because a trigger is the only piece in this course capable of making an UPDATE do something you didn't write.