Two months ago, I reviewed the Packt Publishing book “Instant PostgreSQL Starter” . And two weeks ago, Packt Publishing offered me to review their latest PostgreSQL book, “PostgreSQL Server Programming”.

I was looking forward reading that book. I’m, like a lot of PostgreSQL users, quite fond of using as much server-side features and functions as possible. And at work, when facing people long used to extremely simple queries (even a join seems weird to some of them), it’s quite difficult to teach that no, a SQL server is not a stupid spreadsheet. I thus was expecting that book to help me teach them other ways.

A simple warning : this book is not for beginners. It is aimed at moderate/advanced PostgreSQL users, people with some SQL knowledge already. It’s also not a SQL reference, covering every corner of the language. There is no CTE in this book for instance.

Now that we know what this book is not about… Let’s have a look at the table of contents.

The book is structured in 10 chapters.

PL/pgSQL programming basics

The first four teach you basics about server side programming (declaring variables in PL/pgSQL, returning more than a basic type…)

It begins with a simple introduction to the principles behind server side programming, the way one can view PostgreSQL as a framework, examples where server side shines, some teasing about triggers, and key reasons for server side programming… Then an entire chapter covers the reasons why the author choose PostgreSQL in the first place, the features it brings to you from a programmer point of view… These 40 pages are welcome for the high quantity of information they bring.

Then the fun begins with «Your First PL/pgSQL Function». We learn the basics about PostgreSQL functions writing, passing parameters, returning values, loops, conditional structures, returning a set… What you need to write your functions, you’ll find it here.

And the last «pure» PL/pgSQL chapter teach you some tricks about returning structured data, arrays, using IN/OUT/INOUT parameters, cursors, even a few words about the XML data type.

Triggers, debugging…

The two following chapters cover two must-have for server-side programming.

The triggers are probably the key feature of server-side programming, the feature you really can not do in another way. Not much to tell here. It covers the classical triggers use cases : auditing, disallowing features, modifying an update before it happens.

And the second chapte covers debugging, because like any real programmer, you will need it. It explains the classical debug through «prints», and a real debugger, integrated with pgadmin. Quite a nice tool I did not know about.

Other languages

The book has an entire chapter about unrestricted languages, especially PL/Python. It is really nice because it uncovers a new set a features with it being an unrestricted language, while it remains simple to use and to understand. A good choice for this book.

And a second chapter goes deeper in PostgreSQL with functions written using C. It explains of course why you would have to decide to write such functions (performances, some features…), and what you need to know to not get lost amid the PostgreSQL code.

Final chapters

The last two chapters cover two more advanced cases. The first one teachs you how to use PL/Proxy to improve your scalability by splitting big tables accross servers, using a simple chat system as example. This is the natural evolution after moving more logic in the server and getting too much data load for one server, you have to split up, and PL/Proxy is an easy way to distribute the load accross servers…

And the final chapter covers writing extensions. Extensions are by far the best way to distribute your PostgreSQL addons. Thanks to this book, you can learn how to write new PostgreSQL features, and that chapter just tells you how to distribute them.

 

To make this quick : this book meet my expectations. It’s a handy book to have when using PL/pgSQL and PL/python, it can help convincing people to use server side programming. It’s not a complete reference, it does not cover advanced queries (that would require an entire book, maybe more than one), but it’s more than enough to start and stay on your desk for a while when writing functions…