Outline updated November 2026
PostgreSQL Stored Procedures and Functions
Class Duration
14 hours of live training delivered over 2-4 days to accommodate your scheduling needs
Student Prerequisites
- Prior experience with SQL and relational databases is necessary
- Experience with PostgreSQL is strongly recommended but not required
Target Audience
DBAs, backend engineers, and data engineers who put business logic inside PostgreSQL. New to SQL? Start with SQL Fundamentals. Python teams may prefer PostgreSQL for Python Programmers.
Description
This course teaches stored procedures and functions in PostgreSQL. It is for developers and DBAs who already write SQL. Students start with SQL functions, then learn PL/pgSQL variables, control structures, and exception handling. They write procedures that control transactions, plus triggers and dynamic SQL. The course also covers performance, security, other procedural languages, and testing and debugging.
Learning Outcomes
- Write SQL and PL/pgSQL functions with correct parameters, return types, and volatility.
- Choose between a function and a stored procedure for a task.
- Control transactions with COMMIT and ROLLBACK inside procedures.
- Use PL/pgSQL variables, control structures, and exception handling.
- Work with composite types, cursors, and recursive functions.
- Write trigger functions and use window functions in queries.
- Tune function performance with EXPLAIN ANALYZE and plan caching.
- Secure database code with privileges, SECURITY DEFINER, and injection defenses.
- Build dynamic SQL safely and write functions in PL/Python or PL/Perl.
- Test and debug functions and procedures with pgAdmin and other tools.
Training Materials
All students receive comprehensive courseware covering all topics in the course. Courseware is distributed via GitHub in the form of documentation and extensive code samples. Students practice the topics covered through challenging hands-on lab exercises.
Software Requirements
Students will need a free, personal GitHub account to access the courseware. Students are provided a cloud-based environment for all demonstrations and lab exercises. The course is taught on PostgreSQL 18.
Training Topics
Stored Procedures and Functions in PostgreSQL
- Procedures and functions overview
- Functions vs. procedures
- Use cases and benefits
Basics of SQL Functions
- Creating simple SQL functions
- Parameters and return types
- SQL vs. PL/pgSQL functions
- Function volatility categories
PL/pgSQL: The Procedural Language of PostgreSQL
- PL/pgSQL basics
- Declaring variables and constants
- IF and CASE statements
- LOOP, WHILE, and FOR loops
- Error handling with EXCEPTION blocks
Writing Complex Functions
- Functions and the calling transaction
- Composite and custom data types
- Cursors in functions
- Recursive functions
- Documenting functions
Working with Stored Procedures
- Creating and calling stored procedures
- Transaction control with COMMIT and ROLLBACK
- IN, OUT, and INOUT parameters
- Procedures for complex transactions
- Embedding business logic
- Documenting procedures
Performance Considerations
- Performance tips
- Using EXPLAIN ANALYZE
- Optimizing PL/pgSQL code
- Plan caching
- Concurrency issues
Security and Permissions
- Function security and definer rights
- Permissions on functions and procedures
- Sensitive data in functions
- SQL injection risks and defenses
Advanced Topics
- Dynamic SQL in PL/pgSQL
- Triggers and trigger functions
- Window functions
- PL/Python and PL/Perl
Testing and Debugging
- Testing functions and procedures
- Debugging PL/pgSQL
- Debugging with pgAdmin and other tools