PostgreSQL – Programming in PL/pgSQL and Advanced Development Techniques (PGSQL2)

Databases, PostgreSQL

This intensive hands-on course covers modern server-side development with PostgreSQL. Through practical workshops you will master PL/pgSQL, develop stored procedures, use dynamic SQL safely and implement triggers and set-returning functions for real projects.

The course emphasizes transaction control, security against SQL injection and safe dynamic SQL, plus performance tuning with indexes, partitioning and plan analysis. Participants practice debugging, error handling and deployment best practices for production.

Location, current course term

Brno + online (volitelně)	9/22/2025 - 9/23/2025 CZECH Order 12/15/2025 - 12/16/2025 CZECH Order 3/9/2026 - 3/10/2026 CZECH Order
Praha + online (volitelně)	9/22/2025 - 9/23/2025 CZECH Order 12/15/2025 - 12/16/2025 CZECH Order 3/9/2026 - 3/10/2026 CZECH Order
Custom	Customized Training (date, location, content, duration)

The course:

Hide detail

• Introduction to server-side programming
1. Basic architecture, benefits and limitations of server-side programming
2. Options and languages for server-side - functions and procedures

• Procedures in SQL
1. Inlining SQL procedures
2. Array processing and loops
3. Using CTEs instead of procedures

• Introduction to PL/pgSQL
1. PL/pgSQL basics - block syntax and scoping
2. Variables, parameters and types
3. Control structures

• Error handling and debugging
1. Exceptions and catching them
2. Raising exceptions, assertions
3. Debuggers for PL/pgSQL

• Transactions
1. Isolation levels in PostgreSQL and transaction control in procedures
2. Savepoints, subtransactions and autonomous transactions
3. Preventing errors and locking

• Dynamic SQL
1. Reasons and methods for using dynamic SQL
2. Security and SQL Injection
3. Cursors and working with them

• SRF functions
1. Table-returning functions and their use
2. Performance limitations of table functions

• Triggers
1. Types and capabilities of triggers in PostgreSQL
2. Parameterized, recursive and other advanced techniques
3. Good and bad uses of triggers and performance aspects

• Views
1. Overview and use of views
2. Processing modes — advantages and disadvantages
3. Materialized views

• Temporary tables
1. Use and properties of temporary tables
2. Using temporary tables in PL/pgSQL

• Indexes and basics of optimization
1. Viewing execution plans
2. Basic index types and their usage

• Table partitioning
1. Overview of partitioning types and options
2. Benefits, limitations and comparison with indexes

• Tips for PL/pgSQL development
1. Performance best practices
2. Security best practices

Assumed knowledge:

Basic knowledge of SQL and databases and experience using PostgreSQL.

Recommended previous course:

PostgreSQL – SQL Basics (PGSQL1)

Recommended subsequent course:

PostgreSQL – Administration and System Implementation (PSTGR1)

Schedule:

2 days (9:00 AM - 5:00 PM )

Course price:

576.00 € ( 696.96 € incl. 21% VAT)

Language: