PostgreSQL for Developers [STA_PSQLDEV]

OVERVIEW

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.

OBJECTIVES

Course Objectives

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

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.

NEXT STEP

Further Learning

• PostgreSQL Administration

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