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 seeking to embed business logic in PostgreSQL will master PostgreSQL stored procedures and functions, optimize PL/pgSQL, tune performance, harden security, leverage triggers and window functions, and debug faster to ship scalable, maintainable apps.
Description
This comprehensive course offers professionals an in-depth exploration of Stored Procedures and Functions in PostgreSQL. It begins with an introduction to these database objects, highlighting their differences and advantages. Participants will move on to learn the fundamentals of SQL Functions and PL/pgSQL, gaining practical experience in writing more complex functions. The curriculum also addresses working with Stored Procedures, delves into important topics such as performance, security, and permissions, and guides learners through advanced concepts including dynamic SQL, triggers, window functions, integration with other programming languages, and best practices for testing and debugging. By the end of the course, professionals will have the skills needed to make the most of PostgreSQL and elevate their programming expertise.
Learning Outcomes
- Design and document SQL and PL/pgSQL functions with correct parameters, return types, and volatility (IMMUTABLE/STABLE).
- Decide between functions and stored procedures; use IN/OUT/INOUT parameters and manage transactions safely.
- Implement PL/pgSQL control structures, variables, and exception handling to write clear, maintainable logic.
- Work with composite/custom types, cursors, recursion, triggers, and window functions for complex data workflows.
- Diagnose and tune performance using EXPLAIN/ANALYZE, plan caching behavior, and concurrency strategies.
- Secure database code with proper privileges, SECURITY DEFINER/INVOKER, data-handling patterns, and injection defenses.
- Apply dynamic SQL thoughtfully and integrate with other languages (e.g., PL/Python, PL/Perl) when appropriate.
- Test and debug functions/procedures using pgAdmin and other tools; add logging and reproducible test harnesses.
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.
Training Topics
Introduction
- Overview of Stored Procedures and Functions in PostgreSQL
- Differences between Functions and Procedures
- Use Cases and Benefits
Basics of SQL Functions
- Creating Simple SQL Functions
- Function Parameters and Return Types
- SQL vs. PL/pgSQL Functions
- Immutable and Stable Functions
PL/pgSQL: The Procedural Language of PostgreSQL
- Introduction to PL/pgSQL
- Declaring Variables and Constants
- Control Structures: IF, LOOP, WHILE, FOR
- Error Handling and EXCEPTION blocks
Writing Complex Functions
- Handling Transactions within Functions
- Functions with Composite and Custom Data Types
- Using Cursors in Functions
- Recursive Functions
- Documenting Functions
Working with Stored Procedures
- Creating and Executing Stored Procedures
- Differences Between Procedures and Functions in PostgreSQL
- Passing IN, OUT, and INOUT Parameters
- Using Procedures for Complex Transactions
- Embedding Business Logic
- Documenting Procedures
Performance Considerations
- Function and Procedure Performance Tips
- Using EXPLAIN and ANALYZE
- Optimizing PL/pgSQL Code
- Understanding and Using Plan Caching
- Concurrency Issues
Security and Permissions
- Function Security and Definer Rights
- Managing Permissions for Functions and Procedures
- Handling Sensitive Data within Functions
- SQL Injection Risks and Mitigation
Advanced Topics
- Dynamic SQL in PL/pgSQL
- Triggers and Trigger Functions
- Writing and Using Window Functions
- Integration with Other Languages (Python, Perl, etc.)
Testing and Debugging
- Best Practices for Testing Functions and Procedures
- Debugging Techniques for PL/pgSQL
- Using pgAdmin and Other Tools for Debugging