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.

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.

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 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 )
      );

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
     );

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 )
);

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.

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.

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.

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.