COURSE OBJECTIVE:
Course Objectives To provide a broader set of PostgreSQL skills for someone who currently has a good working knowledge of PostgreSQL.

TARGET AUDIENCE:
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 PREREQUISITES:
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.

COURSE CONTENT:
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

FOLLOW ON COURSES:
Further Learning PostgreSQL Administration