Write server-side database logic in PostgreSQL using PL/pgSQL. You will build functions that return scalars, rows and sets, write procedures with transaction control, handle errors with exception blocks and RAISE, and judge when logic belongs in the database rather than the application.
Watch the free preview
The anatomy of a PL/pgSQL function — free to watch, no account needed.
What you'll learn
- Write server-side database logic in PostgreSQL using PL/pgSQL functions and procedures
- Declare and use variables, IF/CASE conditionals and every PL/pgSQL loop form correctly
- Build functions that return scalar values, single rows and full result sets
- Handle runtime errors with exception blocks and raise diagnostics with RAISE
- Decide when to push logic into the database versus keeping it in the application
Syllabus
PL/pgSQL Structure and Variables
The anatomy of a PL/pgSQL functionFree preview
Variables, assignment and SELECT INTO
Control Flow — Conditionals and Loops
Conditionals with IF and CASE
Loops — LOOP, WHILE, FOR and FOREACH
Functions and Procedures
Functions returning scalars, rows and sets
Procedures, CALL and transaction control
Errors and Design Judgement
Exception handling and RAISE
When to push logic into the database
Lab — 10 Exercises & Solutions
Exercises 1–5
Exercises 6–10