Book Image

PostgreSQL 11 Server Side Programming Quick Start Guide

By : Luca Ferrari

Book Image

PostgreSQL 11 Server Side Programming Quick Start Guide

By: Luca Ferrari

Overview of 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.

Preface

Who this book is for

What this book covers

To get the most out of this book

Free Chapter

PostgreSQL Server-side Programming

PostgreSQL Server-side Programming

What is server-side programming?

The example database

Statement Tricks: UPSERTs, RETURNING, and CTEs

Statement Tricks: UPSERTs, RETURNING, and CTEs

Inserting, updating, or both?

Getting back modified data with RETURNING

Common Table Expressions (CTEs)

The PL/pgSQL Language

The PL/pgSQL Language

An introduction to PL/pgSQL

Variables and variable assignment

Exceptions and error handling

Executing dynamic statements

Throwing away a query result set

The FOUND Global Variable

Stored Procedures

Stored Procedures

Using functions, procedures, and routines

PL/Perl and PL/Java

PL/Perl and PL/Java

Triggers

Data manipulation triggers

Data definition triggers

Rules and the Query Rewriting System

Rules and the Query Rewriting System

Introducing the query rewriting system

Extensions

Extension management

Creating your own extension

Extension data and backups

Adding an object to an existing extension

The PostgreSQL Extension Network (PGXN)

Inter Process Communication and Background Workers

Inter Process Communication and Background Workers

Inter-Process Communication (IPC)

Background Workers

Custom Data Types

Custom Data Types

Custom data types

Composite types

Other Books You May Enjoy

Other Books You May Enjoy

Leave a review - let other readers know what you think

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

Summary

PL/pgSQL is the default language used in PostgreSQL to implement runnable pieces of code. It can be used to implement functions, procedures, triggers, and so on. The language allows you to easily interact with the underlying database while providing foundations for iterations, conditionals, and exception handling, or, in short, all the features of a standalone programming language.

Thanks to the DO statement, it is possible to test and run a piece of code as an anonymous block of code. In the next chapters, we will learn how to use the language to implement reusable pieces of code such as functions and procedures.