PostgreSQL 11 Server Side Programming Quick Start Guide

5 (1 reviews total)
By Luca Ferrari
    Advance your knowledge in tech with a Packt subscription

  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. PostgreSQL Server-side Programming

About this book

PostgreSQL is a rock-solid, scalable, and safe enterprise-level relational database. With a broad range of features and stability, it is ever increasing in popularity.This book shows you how to take advantage of PostgreSQL 11 features for server-side programming. Server-side programming enables strong data encapsulation and coherence.

The book begins with the importance of server-side programming and explains the risks of leaving all the checks outside the database. To build your capabilities further, you will learn how to write stored procedures, both functions and the new PostgreSQL 11 procedures, and create triggers to perform encapsulation and maintain data consistency.

You will also learn how to produce extensions, the easiest way to package your programs for easy and solid deployment on different PostgreSQL installations.

Publication date:
November 2018
Publisher
Packt
Pages
260
ISBN
9781789342222

 

PostgreSQL Server-side Programming

The motto of PostgreSQL is widely known: the most advanced open source database in the world. PostgreSQL is a rock-solid, scalable, and safe enterprise-level relational database that is gaining increasing popularity thanks to its wide variety of features and its stability. It is developed and maintained by a team of database experts, but it is open source, which means it is not a commercial product; it belongs to everyone and everyone can contribute to it. Moreover, thanks to its permissive BSD-style license, it can be released as a custom product, allowing both the marketplace and business opportunities to grow.

The latest release of this database is PostgreSQL 11. This version includes a number of new features on both the core side, such as replication and partitioning, and the in-database development side, such as procedures and improved support for event triggers. Developing within PostgreSQL is fun and easy, as it provides a rich infrastructure for developers to integrate the business logic within the database itself. We can implement this logic in a large set of available languages, including Perl, Python, Java, and Ruby, breaking the restriction of having to carry out all database-related activity in SQL.

This book focuses on the development side of interacting with PostgreSQL, which means embedding the code into the database in order to automate tasks, keep data more coherent by enforcing rules, mangling data, and transforming it. Throughout this book, we will look at two "external" languages: Perl and Java. Choosing which external languages to use was not easy, since PostgreSQL supports a large number of them, but the important concept is that, you, the developer, are free to choose the language you prefer in order to implement server-side programming with PostgreSQL. Of course, as you can imagine and as we will see over the course of the book, this does not mean that any language is appropriate for any task. Languages behave differently because they have different sets of features, different cultures, different ecosystems and libraries, and different support for different tools. Therefore, even though PostgreSQL allows us freedom with regard to the language we use, it is important that we bear in mind that different situations might require different languages.

Why is my favorite language not listed?

PostgreSQL supports a lot of foreign languages, which can be either scripting languages or not. As you can imagine, explaining all the details of every language is outside the scope of this book. However, in order to demonstrate the differences between the native PostgreSQL language (which is often called PL/pgSQL) and foreign languages, we chose to show a well-integrated scripting language, Perl, and a compiled one, Java. The same concepts, advantages, and drawbacks of these two languages can be applied to other languages.

Some examples will be implemented using the C language, which is the language that PostgreSQL itself is implemented in. For this reason, it has better support in PostgreSQL. However, it is possible to almost totally avoid developing in the C language and to opt instead for friendlier and easier languages.

In this chapter, we will take a look at the following topics:

  • What server-side programming is
  • An introduction to the languages that will be used in the rest of the book to implement examples
  • How the book is organized and which topics will be covered in each chapter
  • How to read and understand the code examples
 

What is server-side programming?

Server-side programming is a term used to indicate the development (or programming) of features (such as code) within the server directly, or, in other words, on the server side. It is the opposite of client-side programming, which is where a technological stack accesses the database, manipulates the data, and enforces rules. Server-side programming allows developers to embed business logic directly into the server (such as PostgreSQL), so that it is the duty of the server to run code to enforce constraints and keep the data secure and coherent. Moreover, since server-side programming embeds code in the server, it helps to implement automation.

One advantage of server-side programming is that the code runs locally to the data it uses; no network connection or external resources are required to access the underlying data. This usually means that the code that is embedded into the server runs faster than the client-side code, which requires the user to connect to the database in order to gain access to the data that is stored.

This also means that the client application can exploit embedded code, since it is centralized to the server, without any regard to the technological stack that the client is using. This often speeds up the implementation of applications, since no distributed or external dependencies are required, other than the ones needed by the server itself.

Last but not least, server-side programming allows the code to be stored into the database itself. This means that this code is managed like any other database object and can be backed up and restored with the usual database-backup tools. This is not quite true for the languages that come in a compiled form, such as Java, but having code stored within the database simplifies a lot of the management involved in migrating and upgrading the code regardless. Of course, server-side programming should not be thought of as a comprehensive solution to every problem. If it is used incorrectly, the code stored within the server can make it consume too many resources, including memory and temporary files (and also I/O bandwidth), resulting in the users' data being served at a lower speed. It is therefore really important to exploit server-side programming only in situations in which it makes sense to do so and when it can simplify the management of the data and the code.

How to get help

PostgreSQL is well known for its extensive and accurate documentation. Moreover, the PostgreSQL community is very responsive and collaborative with regard to welcoming and helping new users. Typically, help is provided by the community via both Internet Relay Chat (IRC) channels and mailing lists. Related projects often have their own IRC channels and mailing lists as well.

However, having channels through which we can ask for help does not mean that every question we ask will be answered. Bear in mind that the people behind the mailing lists or the IRC channels are often volunteering their own time. Therefore, before sending a question, be sure to have done your homework by reading the documentation and providing as much information as possible so that other people can replicate and test out your particular problem. This usually means providing a clear indicating of the version of PostgreSQL that you are running, the type and version of the OS that you are using, and a compact and complete SQL example to replicate your scenario. If you do this, you will be astonished at how quickly and accurately the community can help.

 

The example database

This book aims to be a practical guide. For this reason, in the following chapters, you will see several code examples. Instead of building ad-hoc examples for every feature, the book references a small database from a real-world application, in order to show how you can improve your own database with the features covered.

The example database, named testdb, is inspired by an asset-management software that stores file metadata and related tags. A file is something that is stored on a disk and is identified by properties such as a name, a hash (of its content), and a size on the disk. Each file can be categorized with tags, which are labels that are attached to the file itself.

Listing 1 shows the SQL code that generates the structure of the file table, which has the following columns:

  • pk is a surrogate key that is automatically generated by a sequence
  • f_name represents the file name on the disk
  • f_size represents the size of the file on the disk
  • f_type is a textual representation of the file type (for example, MP3 for a music file)
  • f_hash represents a hash of the content of the file

We might want to prevent the addition of two files with the same content hash. In this case, the f_hash column works as a unique key. Another optional constraint is related to file size; since every file on a disk has a size greater or equal to zero (bytes), it is possible to force the f_size column to store only non-negative values. Similarly, the name of the file cannot be unspecified. More constraints can be added; we will cover some of these in the following chapters.

  CREATE TABLE IF NOT EXISTS files (
pk int GENERATED ALWAYS AS IDENTITY,
f_name text NOT NULL,
f_size numeric(15,4) DEFAULT 0,
f_hash text NOT NULL DEFAULT 'N/A',
f_type text DEFAULT 'txt',
ts timestamp DEFAULT now(),
PRIMARY KEY ( pk ),
UNIQUE ( f_hash ),
CHECK ( f_size >= 0 )
);

Listing 1: Code to create the files table

Listing 2 shows the structure of the tags table:

  • pk is a surrogate key that is automatically generated by a sequence.
  • t_name is the tag name.
  • t_child_of is a self-reference to the tuple of another tag. Tags can be nested into each other to build a hierarchy of tags. As an example, let's say the photos tag contains the family and trips tags; these are children of the photos tag. The same tag can appear in different hierarchies, but cannot appear twice in the same position of the same hierarchy. For this reason, a unique constraint over the tag name and its relationship is enforced.
      CREATE TABLE IF NOT EXISTS tags(
pk int GENERATED ALWAYS AS IDENTITY,
t_name text NOT NULL,
t_child_of int,
PRIMARY KEY ( pk ),
FOREIGN KEY ( t_child_of ) REFERENCES tags( pk ),
UNIQUE( t_name, t_child_of )
);
Listing 2: SQL code to generate the tags table structure

Since a file can have multiple tags, a join table has been used to instantiate a many-to-many relationship. Listing 3 shows a join table, which is named j_files_tags. This simply stores a relationship between the tuple of a file and the tuple of a tag, allowing only one association between a file and a tag.

      CREATE TABLE IF NOT EXISTS j_files_tags (
pk int GENERATED ALWAYS AS IDENTITY,
f_pk int,
t_pk int,
PRIMARY KEY ( pk ),
UNIQUE( f_pk, t_pk ),
FOREIGN KEY ( f_pk ) REFERENCES files( pk ) ON DELETE CASCADE,
FOREIGN KEY ( t_pk ) REFERENCES tags( pk ) ON DELETE CASCADE
);
Listing 3: SQL code to create a join table to match files and tag tables data

There are also some other tables that are used to demonstrate particular scenarios. The first is named archive_files, and has the same structure as the files table. The other is named playlist, and represents a very minimalistic music playlist with filenames and a simple structure, as shown in listing 4:

CREATE TABLE IF NOT EXISTS playlist (
pk int GENERATED ALWAYS AS IDENTITY,
p_name text NOT NULL,
PRIMARY KEY ( pk )
);
Listing 4: SQL code to generate the playlist table

We can either construct these tables by hand or by using one of the scripts provided with the book code snippets from the code repository, located at https://github.com/PacktPublishing/PostgreSQL-11-Quick-Start-Guide. In this case, the tables will also be populated with some test data that we can use to show the results of queries that we will be running in the following chapters.

As you can see, each table column has a prefix letter that identifies the table to which it belongs. For example, f_name has a prefix, f, that reminds the files table it belongs to. This can help us to discriminate the columns of a table when they are joined or when a complex query is built. Throughout the examples in this book, the same concept will be applied to different objects. For example, a function will have a name starting with an f, a procedure with a p, a trigger with tr, and so on. While this is not a commonly-used best practice, it can help us to understand the type of an object and its context from its name.

All the examples shown in this book have been tested and run on PostgreSQL 11 on FreeBSD. They should work seamlessly on any other PostgreSQL 11 installation. All the code has been run through the official psql command line client, even if it is possible to run them with other supported clients (such as pgAdmin4).

Most of the code snippets can be executed as a normal database user. This is emphasized in the code by the psql default prompt, which is as follows:

testdb=>

If the code must be run from a database administrator, otherwise known as a superuser, the prompt will change accordingly, as follows:

testdb=#

As well as this, the examples in which superuser privileges are required will be clearly indicated.

Each time we execute a statement via psql, we get a reply that confirms the execution of the statement. If we execute SELECT *, the reply we receive will be a list of tuples. If we execute other statements, we will get a tag that represents the execution of the statement. This is demonstrated in the following examples:

testdb=> INSERT INTO playlist VALUES( ... );
INSERT
testdb=> LISTEN my_channel;
LISTEN
testdb=> CREATE FUNCTION foo() RETURNS VOID AS $$ BEGIN END $$ LANGUAGE plpgsql;
CREATE FUNCTION

So that we can focus on the important parts of a code snippet, we will remove the output reply of each statement execution if it is not important. The preceding listing can therefore be represented in a more concise way, as follows:

testdb=> INSERT INTO playlist VALUES( ... );
testdb=> LISTEN my_channel;
testdb=> CREATE FUNCTION foo() RETURNS VOID AS $$ BEGIN END $$ LANGUAGE plpgsql;

In this way, you will see only the commands and the statements that you have to insert into the server connection.

The source code of the examples in this book

All the source code of the book is available as individual files with the downloadable source code. Almost every file name includes the number of the chapter it belongs to and a suffix that indicates the type of file. In most cases, this will be sql, which denotes the SQL script. Files with the output extension are not runnable code; instead, these are the output of commands. As an example, the Chapter3_Listing01.sql file is the first listing script from Chapter 3, The PL/pgSQL Language, while Chapter3_Listing01.output is the textual result of the execution of the former file. Both are shown with the listing number 3.

Please note that the code formatting in the source files and the code snippets of the book are not exactly the same due to typographical constraints.

 

Summary

PostgreSQL is a powerful and feature-rich enterprise-level database. It allows database administrators and application developers to store code directly in the server and execute code whenever it is required, allowing us to use a server-side-programming approach. The advantage of having code that is executed by the server itself is that it runs nearer the data, it is under the control of the server, and it is centralized, which means that every connection that accesses the data will execute the same code.

Developing in PostgreSQL is fun and powerful, thanks to its rich and modular platform.

About the Author

  • Luca Ferrari

    Luca has been passionate about computer science since the Commodore 64 era, and today holds a master's degree (with honors) and a PhD from the University of Modena and Reggio Emilia. He has written several research papers, technical articles, and book chapters. In 2011, he was named Adjunct Professor by the University of Nipissing. An avid Unix user, he is a strong advocate of open source, and in his free time he collaborates with a few projects. He met PostgreSQL back in release 7.3; he was a founder and former president of the Italian PostgreSQL Community (ITPUG), he talks regularly at technical conferences and events, and delivers professional training. In his teenage years, he was quite a proficient archer. He lives in Italy with his beautiful wife, son, and two (female) cats.

    Browse publications by this author

Latest Reviews

(1 reviews total)
Some intermediate skills in programming needed. I would recommend to author plpython3u to add to this book. Otherwise enough good reading.

Recommended For You

PostgreSQL 11 Server Side Programming Quick Start Guide
Unlock this book and the full library for $5 a month*
Start now