PostgreSQL 11 Server Side Programming Quick Start Guide

Effective database programming and interaction

Luca Ferrari

• Publisher: Packt Publishing
• Year: 2018
• Print ISBN: 9781789342222
• eBook ISBN: 9781789343502
• Edition: 1
• Topic: Computer Science
• Subtopic: Data Mining
• Index: Computer Science

Extend PostgreSQL using PostgreSQL server programming to create, test, debug, and optimize a range of user-defined functions in your favorite programming language

Key Features

• Learn the concepts of PostgreSQL 11 with lots of real-world datasets and examples
• Learn queries, data replication, and database performance
• Extend the functionalities of your PostgreSQL instance to suit your organizational needs

Book Description

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.

What you will learn

• Explore data encapsulation
• Write stored procedures in different languages
• Interact with transactions from within a function
• Get to grips with triggers and rules
• Create and manage custom data types
• Create extensions to package code and data
• Implement background workers and Inter-Process Communication (IPC)
• How to deal with foreign languages, in particular Java and Perl

Who this book is for

This book is for database administrators, data engineers, and database engineers who want to implement advanced functionalities and master complex administrative tasks with PostgreSQL 11.

Stored Procedures

This chapter introduces the main features that allow us to implement Server-Side Programming. Many PostgreSQL features rely on the ability to execute units of code, and, as already discussed, PostgreSQL provides rich support to different languages and execution environments.

In this chapter, we will learn how to write executable code that can be executed directly on the server, how to package it into stored procedures, and how to manage them. In particular, this chapter will focus on the following topics:

• Functions, procedures, and routines
• How to write, modify, and manage a stored procedure
• How to implement a transaction-aware stored procedure

Using functions, procedures, and routines

PostgreSQL 11 provides three main terms to refer to an executable unit of code: functions, procedures, and routines.

A function is what is often called a stored procedure and has always existed in PostgreSQL. A function, generally speaking, is a block of code associated with a mnemonic name. Functions can accept arguments and return a result after they have been executed. Both their arguments and their return values can be either scalar types (such as singles) or complex types (such as tuples). PostgreSQL exploits functions all over the cluster and can be used in queries and statements, to implement trigger behavior and, under particular circumstances, to build indexes.

Functions cannot interact with the transaction layer, which means they execute within the transaction of the invoking statement. Functions can be categorized by their implementation type:

• Normal functions: This usually refers to stored procedures
• Aggregate functions: These operate on a set of tuples and provide an aggregate single result (such as sum())
• Window functions: These perform computations over a set of tuples without reporting a single result (such as rank())

In this chapter, we will learn how to write normal functions, which are the most common ones.

A procedure is a new object introduced with PostgreSQL 11. In short, it is a special function that is able to interact with the transaction layer by committing a part of the work. Even if functions and procedures share several properties, procedures are quite different.

A routine can be either of the aforementioned two kinds of executable code. There is no object of routine type, rather routine is a shorthand to manage either a function or a procedure by either changing it or dropping it. To a certain extent, a routine is a synonym of either a function or a procedure.

The fact that the ROUTINE keyword can be used as a synonym for both a PROCEDURE or a FUNCTION is due to the fact that both objects are stored into the same system catalog, the pg_proc table. The difference between them is found in the value of the prokind field, which has f for functions and p for procedures.

Functions and procedures share definition attributes and properties, the most interesting of which is the ability to implement the executable code in a lot of different languages. In fact, in PostgreSQL, the syntax of the definition of either a procedure or a function has two parts:

• Declaration: This is the definition of the executable code, providing attributes such as the name, the arguments list, and the return types
• Implementation: This is the code that will be executed once the function or the procedure is invoked

The declaration is always expressed by an SQL statement, such as CREATE FUNCTION or CREATE PROCEDURE for a function or a procedure respectively. The implementation can be written in any supported language, meaning we can develop code in non-SQL languages such as Java, Perl, Python, and Ruby. The server is in charge of executing the code with the appropriate technology (such as a virtual machine), marshalling parameters, and return values.

Supported languages

While functions and procedures can be written in plain SQL, we are not likely to end up using this in our day-to-day development. More often, we will write functions and procedures in a much richer language with support for iterations, conditionals, and other constructs to provide a better control flow. PostgreSQL comes with a language named PL/pgSQL that enhances plain SQL with control flow predicates, and is often used as the default language for the implementation of functions and procedures.

Nevertheless, as already stated, PostgreSQL supports other external languages as well. In particular, it can handle Perl 5, Python, and TCL code via built-in languages called plperl, plpythonu, and pltcl, as well as the language PostgreSQL is built on, C. Other languages can be installed and used in addition, including Java (by means of pljava), Ruby, and Bash.

PostgreSQL is written in the C language and, of course, does support it as an external language to build functions and procedures. However, using C is often harder than other languages because it requires a good understanding of PostgreSQL internals.

Usually, all external languages comes in two forms: a trusted and an untrusted version. The trusted language imposes strict constraints on the execution context, not allowing, for instance, the loading of external resources (such as modules, libraries, or files) or network connectivity (such as socket interaction). On the other hand, untrusted languages allow the code to execute whatever the language allows it to do and, for this reason, can only be managed by database superusers.

Functions

Having explained the main concepts behind the PL/pgSQL language, it is now time to turn code snippets into reusable functions.

A function is an entity uniquely identified by a prototype, which is made up of the following:

• A (mnemonic) name
• An argument list
• A return type

PostgreSQL does allow function overloading, which refers to the ability to define several implementations of the same function with the same name. Overloaded functions must have a different prototype, but cannot have a different return type. This means that overloaded functions differ with regard to their argument list (the type and number of arguments).

Each function's prototype is expressed by the CREATE FUNCTION SQL statement, while the implementation of the function can be any supported code block, with plpgsql being the most common language. The declaration of the function must specify the language its implementation code block is expressed in, so that the executor knows how to run the function.

A typical template for declaring a function is as follows:

CREATE FUNCTION foo( arg1, arg2, ...)
RETURNS int
AS <block of code>
LANGUAGE plpgsql;

The function name is specified immediately after the CREATE FUNCTION statement and the argument list is specified in parentheses. The return type value is specified with the RETURNS keyword, and the language in which the function is implemented is specified with the LANGUAGE keyword. The block of code that implements the function is specified after the AS clause, and is usually expressed as a dollar-quoted string. There are more options and properties to declare and define a function, which we will look at later on.

It is interesting...

Table of contents

• Title Page
• Copyright and Credits
• Dedication
• About Packt
• Contributors
• Preface
• PostgreSQL Server-side Programming
• Statement Tricks: UPSERTs, RETURNING, and CTEs
• The PL/pgSQL Language
• Stored Procedures
• PL/Perl and PL/Java
• Triggers
• Rules and the Query Rewriting System
• Extensions
• Inter Process Communication and Background Workers
• Custom Data Types
• Other Books You May Enjoy