This course will allow readers to get a better understanding of PostgreSQL. The course aims to present the readers with a solid knowledge of PostgreSQL building blocks, including the plpgsql language and how it can be used to build stored procedures and triggers. Advanced features like Common Table Expression and Window Functions will be presented, allowing the user to improve her SQL skills and know how to write better and more readable queries.
The reader will know how to manage and understand its database cluster thanks to glance at the PostgreSQL catalog and statistic collector. Last, readers will learn how to handle master-slave replication, a core feature of PostgreSQL.
The plpgsql language
The DO block
Glance at plperl
DML Trigger Types
Implementing triggers with plpgsql
Introduction of cursors
Example of usage of a cursor
Users and Permission Management
Users, Groups and Roles
Allowing permissions and denying permissions
Row Level Security
Introduction to the Query Rewrite System
An example of rule
Common Table Expression
Introduction to CTEs
An example of move
Introduction to Window Functions
A few useful window functions
Configuring the Server
Monitor the database activity
Point in Time Recovery
Glance at Logical Replication
Instructor: Luca Ferrari
Luca lives in Italy with his beautiful wife, his great son and two female cats.
Computer science passionate since the Commodore 64 age, he holds a Master Degree and a PhD in Computer Science. He is a PostgreSQL enthusiast, and has adopted this such database back at version 7.3. He was a co-founder of the Italian PostgreSQL Users’ Group, being also a former president of the latter. He has written several articles about PostgreSQL and teaches at several events and training courses.
Luca is also a Perl lover, an Operating System passionate, a Unix fan and performs as much tasks as possible within Emacs. He considers the Open Source the only sane way of doing software and services. His web site is available at http://fluca1978.github.io