PostgreSQL for Developers

Who will the Course Benefit?

Anyone who has a working knowledge of PostgreSQL but needs to enhance this to include PL/pgSQL,additional functions,and techniques to monitor and optimise query performance.

Course Objectives

To provide a broader set of PostgreSQL skills for someone who currently has a good working knowledge of PostgreSQL.

PostgreSQL for Developers Training Course Course Contents - DAY 1

Course Introduction

• Administration and course materials
• Course structure and agenda
• Delegate and trainer introductions

Session 1: ADVANCED SQL LANGUAGE

• Advanced Datatypes
• Data Type Conversion

Session 2: ENHANCED GROUPING FEATURES

• Revision of aggregate functions and basic GROUP BY and HAVING clauses
• ROLLUP extension
• CUBE extension
• GROUPING function
• GROUPING SETS function

Session 3: ANALYTICAL QUERIES

• Ranking functions
• NTILE and WIDTH_BUCKET functions
• Analytic aggregates
• Windowing functions
• Row and Range specifications
• FIRST_VALUE and LAST_VALUE
• LAG and LEAD functions

Session 4: RECURSIVE QUERIES

• The WITH clause
• Recursive Common Table Expressions

Session 5: REGULAR EXPRESSION SUPPORT

• Match operators and notation
• Repetition operators
• Regular Expression functions
• Sub-expression grouping
• SQL Regular Expressions

Session 6: SERVER PROGRAMMING BASICS

• Extending SQL with Procedural Code
• Basic Elements
• Variables and Constants
• Data Types
• Initialising Variables and Assigning Values
• Using DML Statements in Code
• Generating Output

PostgreSQL for Developers Training Course Course Contents - DAY 2

Session 7: PROGRAM LOGIC

• IF THEN ELSIF ELSE Statements
• CASE Statements
• The Basic Loop Construct
• WHILE and FOR Loops
• Nested and Labelled Loops
• The CONTINUE Statement

Session 8: CURSORS

• What is a Cursor?
• Implicit and Explicit Cursors
• Cursor Operations
• Declaring,Opening and Closing Cursors
• Fetching Rows
• Status Checking
• Where current of clause
• Unbound cursors (refcursors)
• The Cursor FOR Loop
• Parameterised Cursors

Session 9: EXCEPTION HANDLING

• Errors and Messages
• The EXCEPTION clause
• System Raised Exceptions
• The RAISE statement
• STRICT option in a SELECT ..INTO
• Programmer Raised Exceptions
• The GET STACKED DIAGNOSTICS command
• Nested and Labelled Blocks
• Scope of Variables and Cursors

Session 10: USER-DEFINED FUNCTIONS

• CREATE FUNCTION statement
• Function parameters
• Functions Returning Void
• Execute a function using PERFORM
• Listing Functions
• ALTER and DROP FUNCTION statements

Session 11: USER-DEFINED PROCEDURES

• CREATE PROCEDURE statement
• Procedure parameters
• Invoke a procedure using CALL
• Definer's and Invoker's rights execution
• ALTER and DROP PROCEDURE statements

PostgreSQL for Developers Training Course Course Contents - DAY 3

Session 12: TRIGGERS

• DML Triggers
• The Trigger Function
• The CREATE TRIGGER Statement
• BEFORE,AFTER and INSTEAD OF Triggers
• The OLD and NEW qualifiers
• Errors in Triggers
• DDL and Database Event Triggers
• Managing Triggers
• Privileges to create Triggers

Session 13: TRANSACTIONS AND CONCURRENCY

• Overview of Transaction Processing in PostgreSQL
• Transaction Control
• COMMIT,ROLLBACK and SAVEPOINT statements
• AUTOCOMMIT
• Multi-version Concurrency Control (MVCC)
• Transaction Isolation Levels
• Locking Concepts
• Implicit and Explicit Locking of Tables and Rows
• Possible Causes of Contention
• Deadlocks
• Advisory Locks
• Lock Management Parameters

Session 14: SQL TUNING

• Query Optimization
• Scan Methods
• Join Methods
• Join Order
• Statement Transformation
• Detect Slow Queries
• View Execution Plans using EXPLAIN
• Gather Optimizer Statistics with ANALYZE
• Gather Extended Statistics with CREATE STATISTICS
• Parameters that affect Optimization
• Memory Settings that affect Query Performance
• Overview of declarative Table Partitioning

Session 15: LOADING AND UNLOADING DATA

• Import and Export Operations with COPY
• Export using the COPY Command
• Import using the COPY Command

Requirements

This course assumes a good working knowledge of PostgreSQL SQL. This can be obtained by attendance on the pre-requisite SQL for PostgreSQL course.

Further Learning

• PostgreSQL Administration