Server-side programming in PostgreSQL: PL/pgSQL
You write functions, procedures and triggers in PL/pgSQL and learn error handling, safe use of dynamic SQL and testing database code with pgTAP.
Level 301 · 17 lessons · 15 practice labs
Lessons
Functions
- SQL functions
- PL/pgSQL blocks, variables and conditions
- Loops and cursors
- Set-returning functions
- Volatility categories and SECURITY DEFINER
- Short task: a function that calculates discounts
Procedures and triggers
- Procedures and transaction control
- Catching and raising errors
- Row triggers: validation and timestamps
- A change history trigger
- Safe dynamic SQL: format and quote_ident
- Short task: a trigger that reduces stock
Quality
- Testing with pgTAP
- Debugging with RAISE NOTICE
- Function performance and inlining
- Versioning database code
- Capstone: a leave request system
Practice labs
- Write a discount function: Write a PL/pgSQL function that returns a discount by basket total and membership level.
- Write a set-returning function: Write a function that takes a date range and returns order summary rows per day.
- Close a SECURITY DEFINER hole: Show how a privileged function without a set search_path can be abused and fix it.
- Use intermediate COMMITs in a procedure: Write a procedure that updates a million rows in ten-thousand-row batches with a COMMIT after each.
- Raise and catch meaningful errors: Build a flow that raises a custom error code on an invalid leave date and catches it in the caller.
- Roll back partial work on error: Write a batch procedure that skips only the failing record on an inner-block error and logs it to an error table.
- Reduce stock with a trigger after a sale: Write a trigger that reduces product stock when an order line is added and errors if stock is short.
- Keep change history as JSON: Build a generic trigger that writes old and new rows as JSONB to an audit table on every change.
- Untangle trigger order effects: Find and fix a wrong total caused by the order of three triggers on the same table.
- Build a table name safely: Write safe dynamic SQL with format and %I in a function that picks a table by month name.
- Fix a function open to injection: Try an injection on a search function that concatenates text into a query and parameterise it.
- Write dynamic partition maintenance: Write a safe dynamic procedure that creates next month's partition and detaches partitions older than 12 months.
- Trace a function with RAISE NOTICE: Add intermediate output to a looping function giving wrong results and find the faulty step.
- Write function tests with pgTAP: Write and run five pgTAP tests covering edge cases of a leave calculation function.
- Inline a slow function into the query: Turn a function slowed by per-row calls into an inlinable SQL function and compare the plans.
About this course
You write functions, procedures and triggers in PL/pgSQL and learn error handling, safe use of dynamic SQL and testing database code with pgTAP.
What you will learn
- Functions
- Procedures and triggers
- Quality
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.