Postgres in depth · Chapter 35 of 65
Functions
By the end of this chapter you can put a calculation or a query inside the database under a name, write logic with variables, conditions and errors in PL/pgSQL, and tell Postgres how your function behaves so it can use it well. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: The Postgres flavour
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
The checkout, the admin panel and Arjun's revenue report all work out a discounted price, and they round it three different ways: a student was charged 1749.30 and the receipt said 1749.29. Sana wants one rule for "is this coupon usable now?" with a clear reason when it is not, instead of three apps that each forget a case. Priya wants a nightly job that expires old pending orders, run with one command.
A view (chapter 34) names a query, but it takes no arguments, cannot raise an error with a message, and cannot run several steps. A function can.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
discounted.sql: one rule for discounted prices
Create a function discounted_price(price numeric, percent_off integer) that returns the price after the discount, rounded to 2 decimals: price times (100 minus percent_off) divided by 100.
For example, discounted_price(999.99, 33) is 669.99. Mark it with the volatility that fits a function of its arguments alone.
- Task 2
students-in.sql: a function that returns rows
Create a function students_in(course_slug text) that returns one row per student enrolled in the course with that slug, with two columns: name (text) and enrolled_at (timestamptz). An unknown slug returns no rows.
Mark it with the right volatility: it only reads the database.
- Task 3
coupon-price.sql: a coupon checker in PL/pgSQL
Create a PL/pgSQL function coupon_price(coupon_code text, list_price numeric, at_time timestamptz) that returns the list price after the coupon's percent_off, rounded to 2 decimals. It refuses, with exactly these errors:
coupon CODE does not exist (no coupon with that code); coupon CODE is not valid at that time (at_time before valid_from or after valid_until; the last minute is still valid); coupon CODE is used up (max_uses is set and used_count has reached it). CODE is the code that was passed in. Mark it STABLE.
- Task 4 · interview question
Interview: expire-orders.sql, a nightly procedure
Create a procedure expire_pending_orders(cutoff timestamptz) that sets the status of every pending order created before the cutoff to failed, and prints a notice saying how many it expired.
Creating it must change no orders; the Check then runs it with CALL for the cutoff 2026-06-01 00:00 India time.
What you will learn
- SQL functions: CREATE FUNCTION, arguments, RETURNS, a body in $$.
- Set-returning functions: RETURNS TABLE, used in FROM.
- PL/pgSQL: variables, SELECT INTO, IF, FOUND, RAISE EXCEPTION.
- Volatility: IMMUTABLE, STABLE, VOLATILE, and why the promise matters.
- Procedures: CREATE PROCEDURE, CALL, and how they differ from functions.
More in The Postgres flavour
Build "Functions" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.