PostgreSQL 11 Server Side Programming - Quick Start Guide

Yesterday my first commercial book in the PostgreSQL landscape was officially published: PostgreSQL 11 Server Side Programming Quick Start Guide!

PostgreSQL 11 Server Side Programming Book

Is this only about version 11?

The book code has been tested against version 11, but despite the title it is enough general to be used as a basis for pretty much any recent version of PostgreSQL. Of course, some parts cannot be executed on older versions, like for instance the procedures chapter. However, concepts and samples can be adapted also to older versions and are therefore enough general to get a good introduction to server side programming.

What this book covers

This book is a Quick Start Guide, so it is a short book on a very specific subject, that means it will not teach you about PostgreSQL management in general, and assumes you are able to install, run and connect to a PostgreSQL 11 instance.

Table of Contents

The book is organized in 10 chapters:
  • Chapter 1, Introduction to Server Side Programming;
  • Chapter 2, Query Tricks;
  • Chapter 3, The PL/pgSQL Language;
  • Chapter 4, Stored Procedures;
  • Chapter 5, PL/Perl and PL/Java;
  • Chapter 6, Triggers;
  • Chapter 7, Rules and the Query Rewriting System;
  • Chapter 8, Extensions;
  • Chapter 9, Intra-Process Communications;
  • Chapter 10, Custom Data Types;

Description of the Content

The book starts with a chapter dedicated to query tricks: the usage of UPSERT, RETURNING and recursive CTE in order to take advantage of these PostgreSQL features when dealing with common day-to-day tasks.
The the book shows how to use the PL/pgSQL language to write runnable code directly within a database connection. Unlike other books, that show such language by means of functions, I decided to use the DO construct to write and run inline code immediatly.

After that, the reader is ready to learn about FUNCTIONs and PROCEDUREs, the latest new cool object from PostgreSQL 11: both allow you to store runnable code within the server and call it (i.e., run it) later on, but while the former (functions) cannot interact with transaction, the latter (procedures) can.

One interesting and very cool feature of PostgreSQL is its capability to accept code written in pretty much any programming language out there, and therefore why not showing it to the reader? Therefore a chapter is dedicated to the PL/Perl and PL/Java languages. Why such languegs? Because one is a script-like language well supported by PostgreSQL, to the extent it can be executed even in a DO statement, the latter is compiled-like code that require more attention to be deployed and executed. The idea is therefore to present the reader with two possible concrete choices of languages to implement code, and reason about their capabilities and deployment. Of course, you could complain about your favourite language not being in the book, but if you think about, the deployment of Perl code is similar to the deployment of Python code, or Ruby, while Java is similar to the deployment of C#, or…you name it!

With the foundation about the deployable code, it is then possible to implement triggers, and therefore a whole chapter is dedicated to them, including DDL triggers. And since triggers can sometimes be replaced by rules, and the latter are useful for other server-side programming stuff, a chapter is dedicated to rules.

Having learned about how to write code and deploy it, it is a good habit to package your code and mantain it. Luckily PostgreSQL provides extensions to the rescue, so the reader is presented with a guide about how to implement its own extension from scratch, as well as how to interact with the existent extension ecosystem (PGXN and PGXS).

The last two chapter go a little more into details about PostgreSQL internals, showing how to interact among processes (LISTEN and NOTIFY), how to scratch a Background Worker and how to implement custom data types.

I tried to keep the book within a pragmatic approach, showing common use cases and practical solutions. I really hope the book will meet the readers’ wills!

Code Examples

The code examples, listings and outputs are available in the official book repository on GitHub and as a clone on my GitLab repository

Lesson Learned

While writing a book is a long task, it can be fun. I don’t want to minimize it, but it is surely a way to force yourself to explain concept you already know to someone else, therefore improving your knowledge on the subject. Furthermore, writing a book about something you care about, as it was for me, is really fun and satisfactory.

This is not the only book on PostgreSQL I’ve written this year!

While this is surely the book in my professional year, there is also another small book made by lectures on PostgreSQL I’ve done in the past: see the BSDMagazine Course Book Post.

Spread the word!

Once the book has been made available, I did my best effort to make possible readers aware of it, so the following is cronologically ordered list of places where news about the book have been published:
Plant PostgreSQL Italian
Conoscere Linux Mailing List
Planet PostgreSQL
Planet PostgreSQL Feed
  • PgTraining official web site, and I’m really proud about that because they are very smart PostgreSQL professionals!

If you are aware of some other Users’ Group or mailing list or place that can be interest in knowing about the book, please make it aware of, or drop me an email and I will do!

Just in Time for Christmas!

Thanks to the people at Packt, I got my copies before Christmas!

Printed copy of the book

And in the meantime I’ve also created my Amazon Author Profile.

The article PostgreSQL 11 Server Side Programming - Quick Start Guide has been posted by Luca Ferrari on November 30, 2018