PostgreSQL for Developers Course Overview This course covers important basic and intermediate level details for developers interacting with PostgreSQL. Delegates will learn techniques,syntax and structures needed to develop quality applications using the PostgreSQL backend. This course also covers SQL Tuning best practices for writing efficient SQL. The target audience includes developers and programmers who want to extend their knowledge of PostgreSQL to encompass advanced SQL techniques and the usage of Stored Program Units such as triggers,procedures and functions. Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.
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
COURSE OBJECTIVE:
Course Objectives To provide a broader set of PostgreSQL skills for someone who currently has a good working knowledge of PostgreSQL.
FOLLOW ON COURSES:
Further Learning PostgreSQL Administration