Skip to main content

PostgreSQL Stored Procedures and Functions

Executive Summary

This course teaches developers how to write and optimize stored procedures and functions in PostgreSQL. Learn PL/pgSQL, dynamic SQL, and triggers while mastering performance, security, and transaction handling. You’ll also explore window functions, testing, and debugging techniques. Ideal for professionals building powerful, efficient backend logic in PostgreSQL.

Programming Professionals Collaborating

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.

Objectives

  • Understand the overview, differences, and benefits of Stored Procedures and Functions in PostgreSQL.
  • Learn the basics of SQL Functions including creation, parameters, return types, and differences between SQL and PL/pgSQL functions.
  • Master the procedural language of PostgreSQL, PL/pgSQL, including variable declaration, control structures, and error handling.
  • Write complex functions handling transactions, using composite and custom data types, cursors, recursion, and documentation.
  • Work with Stored Procedures, understanding their creation, execution, differences from functions, parameter passing, and embedding business logic.
  • Gain insights into performance considerations, optimization techniques, understanding plan caching, and concurrency issues.
  • Learn about security and permissions, managing function security, permissions, handling sensitive data, and SQL injection risks.
  • Explore advanced topics such as dynamic SQL, triggers, window functions, integration with other languages, and best practices for testing and debugging.

Duration

21 hours of intensive training with live instruction delivered over three to fives days to accommodate varied scheduling needs.

Request Information

Course Outline

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

Prerequisites

  • Prior experience with SQL and relational databases is necessary.
  • Experience with PostgreSQL is strongly recommended but not required.

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. 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.