Outline updated November 2026

Intermediate14 hours delivered over 2-4 daysLive, online or on-site

Request this course

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

Private team training

Bring this course to your team

Tell us about your team and we will tailor this outline to your tools, stack, and schedule. Courses can also be combined into a longer program.

  1. Tell us what you needYour tools, stack, team size, and timeline.
  2. We send a proposalA course or program, an outline adjusted to your team, and possible dates.
  3. Your team trains liveOnline or on-site, with courseware for every student.

Prefer to talk? Call +1 434-509-6890 or email help@cloudcontraptions.com.

We reply within 1 business day.

Training partner?