Book Image

Learning PostgreSQL 10 - Second Edition

By : Salahaldin Juba, Andrey Volkov, Salahaldin Juba
Book Image

Learning PostgreSQL 10 - Second Edition

By: Salahaldin Juba, Andrey Volkov, Salahaldin Juba

Overview of this book

PostgreSQL is one of the most popular open source databases in the world, supporting the most advanced features included in SQL standards. This book will familiarize you with the latest features released in PostgreSQL 10. We’ll start with a thorough introduction to PostgreSQL and the new features introduced in PostgreSQL 10. We’ll cover the Data Definition Language (DDL) with an emphasis on PostgreSQL, and the common DDL commands supported by ANSI SQL. You’ll learn to create tables, define integrity constraints, build indexes, and set up views and other schema objects. Moving on, we’ll cover the concepts of Data Manipulation Language (DML) and PostgreSQL server-side programming capabilities using PL/pgSQL. We’ll also explore the NoSQL capabilities of PostgreSQL and connect to your PostgreSQL database to manipulate data objects. By the end of this book, you’ll have a thorough understanding of the basics of PostgreSQL 10 and will have the necessary skills to build efficient database solutions.
Table of Contents (23 chapters)
Title Page
Credits
About the Authors
About the Reviewers
www.PacktPub.com
Customer Feedback
Preface

Chapter 7. Server-Side Programming with PL/pgSQL

The ability to write functions in PostgreSQL is an amazing feature. One can perform any task within the scope of the database server. These tasks might be related directly to data manipulation such as data aggregation and auditing, or used to perform miscellaneous services such as statistics collection, monitoring, system information acquisition, and job scheduling. In this chapter, our focus is on the PL/pgSQL language. PL/pgSQL is the default procedural language for PostgreSQL and it is full featured. As mentioned earlier, in Chapter 04, PostgreSQL Advanced Building Blocks, PL/pgSQL is installed by default in PostgreSQL.

PL/pgSQL has been influenced by the PL/SQL language, which is the Oracle stored procedural language. PL/pgSQL is a complete procedural language with rich control structures and full integration with the PostgreSQL trigger, index, rule, user-defined data type, and operator objects. There are several advantages to using PL/pgSQL; they are as follows:

  • It is easy to learn and use
  • It has very good support and documentation
  • It has very flexible result data types, and it supports polymorphism
  • It can return scalar values and sets using different return methods

The topics that will be covered in this chapter include:

  • SQL language and PL/pgSQL – a comparison
  • PostgreSQL function parameters
  • The PostgreSQL PL/pgSQL control statements
  • Function predefined variables
  • Exception handling
  • Dynamic SQL