PostgreSQL 11 Server Side Programming Quick Start Guide

By : Ferrari

5 (1)

Buy this Book

PostgreSQL 11 Server Side Programming Quick Start Guide

5 (1)

By: Ferrari

Buy this Book

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

Get in touch

Free Chapter

PostgreSQL Server-side Programming

What is server-side programming?

The example database

Summary

Statement Tricks: UPSERTs, RETURNING, and CTEs

Inserting, updating, or both?

Getting back modified data with RETURNING

Common Table Expressions (CTEs)

Summary

References

The PL/pgSQL Language

An introduction to PL/pgSQL

Variables and variable assignment

Conditionals

Iterations

Exceptions and error handling

Executing dynamic statements

Throwing away a query result set

The FOUND Global Variable

Summary

References

Stored Procedures

Using functions, procedures, and routines

Functions

Procedures

Summary

References

PL/Perl and PL/Java

PL/Perl

PL/Java

Summary

References

Triggers

Data manipulation triggers

Data definition triggers

Summary

References

Rules and the Query Rewriting System

Introducing the query rewriting system

Rules

Summary

References

Extensions

Extension management

Creating your own extension

Extension data and backups

Adding an object to an existing extension

The PostgreSQL Extension Network (PGXN)

Summary

References

Inter Process Communication and Background Workers

Inter-Process Communication (IPC)

Background Workers

Summary

References

Custom Data Types

Custom data types

Enumerations

Composite types

Basic types

Summary

References

Other Books You May Enjoy

Leave a review - let other readers know what you think

Customer Reviews

5 (1)

5 star

100%

4 star

3 star

2 star

1 star

Summary

One powerful feature of PostgreSQL is its ability to host pretty much any programming language on the server-side, meaning that code can be deployed to the server and run locally on the data.

This chapter has shown you how to use Perl 5 and Java as hosted—or foreign—languages. The choice of which language to use as an alternative to SQL or PL/pgSQL depends on several factors, including the knowledge of the language and the specific task to be accomplished. This decision might also be driven by the deployment phase; for example, it is much simpler to deploy a Perl 5 function or procedure than a Java one. Another influencing factor might be the ecosystem of the language, including the libraries and the existing code.

In the next chapter, we will use functions to implement triggers, a way to react to data changes from within the database itself. The aim is...