Advanced PostgreSQL: Stored Procedures and Functions
Duration
3 days
Description
This comprehensive course provides a deep dive into Stored Procedures and Functions in PostgreSQL for professionals. It starts with an introduction to Stored Procedures and Functions, their differences, and benefits. The course then progresses to the basics of SQL Functions, PL/pgSQL, and writing complex functions. It also covers working with Stored Procedures, performance considerations, security, and permissions. The course concludes with advanced topics like dynamic SQL, triggers, window functions, integration with other languages, and best practices for testing and debugging. This course is designed to equip professionals with the necessary skills to optimize their use of PostgreSQL and enhance their programming capabilities.
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.
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.
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.
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
- Conclusion and Next Steps
- Summary of Key Concepts
- Additional Resources for Learning