PostgreSQL for Developers (STA_PSQLDEV)

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