36,59 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
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.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 342
Veröffentlichungsjahr: 2018
Ähnliche
PostgreSQL 11 Server Side Programming Quick Start Guide
Copyright © 2018 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
Commissioning Editor: Amey VarangaonkarAcquisition Editor: Namrata PatilContent Development Editor: Mohammed Yusuf ImaratwaleTechnical Editor: Shweta JadhavCopy Editor: Safis EditingProject Coordinator: Hardik BhindeProofreader: Safis EditingIndexer:Pratik ShirodkarGraphics: Jason MonteiroProduction Coordinator:Jyoti Chauhan
First published: November 2018
Production reference: 1271118
Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK.
ISBN 978-1-78934-222-2
www.packtpub.com
To my beautiful wife, Emanuela; I love her like Santa loves his reindeer. To my great son, Diego, who has changed our lives on 1283788200. To my parents, Miriam and Anselmo; my greatest fans since day one.
Mapt is an online digital library that gives you full access to over 5,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.
Why subscribe?
Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals
Improve your learning with Skill Plans built especially for you
Get a free eBook or video every month
Mapt is fully searchable
Copy and paste, print, and bookmark content
Packt.com
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.packt.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.packt.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.
Contributors
About the author
Luca has been passionate about computer science since the Commodore 64 era, and today holds a master's degree (with honors) and a PhD from the University of Modena and Reggio Emilia. He has written several research papers, technical articles, and book chapters.
In 2011, he was named Adjunct Professor by the University of Nipissing. An avid Unix user, he is a strong advocate of open source, and in his free time he collaborates with a few projects. He met PostgreSQL back in release 7.3; he was a founder and former president of the Italian PostgreSQL Community (ITPUG), he talks regularly at technical conferences and events, and delivers professional training. In his teenage years, he was quite a proficient archer. He lives in Italy with his beautiful wife, son, and two (female) cats.
About the reviewers
Sheldon Strauch is a 20-year veteran of software consulting at companies such as IBM, Sears, Ernst & Young, and Kraft Foods. He has a bachelor's degree in business administration and leverages his technical skills to improve businesses' self-awareness. His interests include data gathering, management, and mining; maps and mapping; business intelligence; and the application of data analysis for continuous improvement. He is currently focused on the development of end-to-end data management and mining at Enova International, a financial services company located in Chicago. In his spare time, he enjoys the performing arts, particularly music, and traveling with his wife, Marilyn.
Andrew Caya started programming computers in GW-BASIC and QBASIC in the early 90s. Before becoming a PHP developer almost 10 years ago, he did some software development in C, C++, and Perl. He is now a Zend-Certified PHP Engineer and a Zend-Certified Architect. He is also the creator of Linux for PHP, the lead developer of a popular Joomla extension, and a contributor to many open source projects.
He is currently CEO, CTO, and founder of Foreach Code Factory, an instructor at Concordia University, an author and a technical reviewer for Packt Publishing, and a loving husband and father.
Packt is searching for authors like you
If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.
Table of Contents
Title Page
Copyright and Credits
PostgreSQL 11 Server Side Programming Quick Start Guide
Dedication
About Packt
Why subscribe?
Packt.com
Contributors
About the author
About the reviewers
Packt is searching for authors like you
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Conventions used
Get in touch
Reviews
PostgreSQL Server-side Programming
What is server-side programming?
How to get help
The example database
The source code of the examples in this book
Summary
Statement Tricks: UPSERTs, RETURNING, and CTEs
Inserting, updating, or both?
Getting back modified data with RETURNING
Common Table Expressions (CTEs)
Introducing CTEs
Writable CTEs and the RETURNING clause: Pipelining statements
Recursive CTEs
Summary
References
The PL/pgSQL Language
An introduction to PL/pgSQL
Variables and variable assignment
Conditionals
Iterations
Exceptions and error handling
The RAISE statement revisited
Executing dynamic statements
Throwing away a query result set
The FOUND Global Variable
Summary
References
Stored Procedures
Using functions, procedures, and routines
Supported languages
Functions
Return Types
 Function argument list
Returning values to the caller
Security
Immutability
Costs
Moving functions to other schemas
Temporary functions
Where is my function?
Permissions
A tag insertion function
Compile and runtime problem detection
Procedures
Procedure argument list
Returning values to the caller
Security
Permissions
Moving procedures to other schemas
Temporary procedures
Where is my procedure?
Interacting with transactions
Nesting transactions and procedures
Summary
References
PL/Perl and PL/Java
PL/Perl
Database interaction
Function arguments and return types
Re-implementing functions in PL/Perl
Implementing routines in PL/Perl
PL/Java
Installing PL/Java
PL/Java main concepts
Implementing a PL/Java function without a deployment descriptor
Implementing a PL/Java function with a deployment descriptor
Summary
References
Triggers
Data manipulation triggers
The trigger function
Creating a simple trigger
Discovering if a table has triggers
Disabling Triggers
Using triggers to modify data on the fly
Parametric triggers
Aborting the current statement
The WHEN condition
The REFERENCING clause
Using a trigger to perform multiple statements
Trigger examples in foreign languages
PL/Perl Triggers
PL/Java Triggers
Data definition triggers
The trigger function
A first event trigger
Using event triggers to avoid specific commands
Filtering specific commands
Managing event triggers
Event trigger examples in foreign languages
PL/Perl
PL/Java
Summary
References
Rules and the Query Rewriting System
Introducing the query rewriting system
What is the query rewriting system?
When and where are rules used by PostgreSQL?
Rules
The syntax of rules
A rule example – archiving tuples on deletion
Where is my rule?
Conditional rules
Using a rule to execute multiple statements– the playlist example
Using a rule to route tuples
When to use rules and when to use triggers
Summary
References
Extensions
Extensions
The control file
The script file
Relocatable extensions
Extension management
Creating and removing extensions
Which extensions are installed?
Creating your own extension
Starting from scratch
Creating an improved version
Installing all the preceding versions
Extension data and backups
Adding an object to an existing extension
The PostgreSQL Extension Network (PGXN)
The extension ecosystem
Getting an extension from the search website
The PGXN Client
Summary
References
Inter Process Communication and Background Workers
Inter-Process Communication (IPC)
Asynchronous events
Limitations of events
A single process example
Event special functions
Channels and event queues
An example of IPC between two database backends
An example of IPC between different applications
Background Workers
Implementing a Background Worker
An example implementation of a Background Worker
The module entry point
Background Worker Main Function
Module Signal Handlers
Deploying and starting the Background Worker
Signaling the Background Worker
Summary
References
Custom Data Types
Custom data types
Enumerations
Creating an enumeration
Type safety
Adding values to an existing enumeration
Where is my enumeration?
Changing an existing label
Composite types
Creating a composite type
Adding or removing attributes to composite types
Type safety
Where is my composite type?
Basic types
Basic type example – image resolution
Defining the textual representation
Defining the basic type and helper functions
Defining the input and output functions
The Makefile
Creating the glue code
Summary
References
Other Books You May Enjoy
Leave a review - let other readers know what you think
Preface
This book will guide you through the development of code inside PostgreSQL 11, the world's most advanced open source database.
PostgreSQL has grown a lot from being a relation database to a whole ecosystem, and has a very rich feature set, a professional and supportive community, and delivers a high quality rock-solid DBMS with outstanding documentation. Thanks to its features and capabilities, PostgreSQL is every day embraced by more and more professionals and industries, and it is for this reason that it is important to understand what is possible with this great database.
Who this book is for
This book is intended for developers and database administrators who already know a few of the basic concepts about relational databases, SQL statements, and transactions. Because the installation and configuration of PostgreSQL is out of the scope of this book, readers should already be familiar with how to run and interact with PostgreSQL.
In this book, we are going to learn about the main features that PostgreSQL provides in order to ease the development of code on the database side, as well as how to use code to implement business rules and keep data under control and consistent. As it is focused on the development side, this book does not cover other topics related to the database, such as tuning, query optimization, privileges, or replication.
What this book covers
This book aims to teach the reader how powerful server-side programming can be in PostgreSQL. At the same time, we are going to learn how fun can it be to implement even complex tasks directly in the database engine. This book is structured as follows:
Chapter 1, PostgreSQL Server-side Programming, presents the idea behind server-side programming and the relationship with PostgreSQL.
Chapter 2, Statement Tricks: UPSERTs, RETURNING, and CTEs, explains some of the powerful features of handling SQL statements that are supported by PostgreSQL, such as obtaining automatically computed data, resolving insert conflicts, and performing looping and joins.
Chapter 3, The PL/pgSQL Language, discusses the default language that can be used to implement code in PostgreSQL. This is an imperative, SQL-like language.
Chapter 4, Stored Procedures, teaches you how to store pieces of code in the server in order to execute them and re-use them later.
Chapter 5, PL/Perl and PL/Java, provides insights into how to use Perl and Java within the PostgreSQL server to implement stored procedures and run code.
Chapter 6, Triggers, explains how to run code that can react to data change events and data definition change events.
Chapter 7, Rules and the Query Rewriting System, explains how PostgreSQL can be used to transform statements into other forms.
Chapter 8, Extensions, teaches you how to organize your code in a way that PostgreSQL can handle and manage, as well as how to deal with updates.
Chapter 9, Intra-Process Communication and Background Workers, looks at how to interact with processes (database connections) and how to plug your own processes into the server.
Chapter 10, Custom Data Types, shows how it is possible to extend the already rich PostgreSQL data type to implement your own type with specific business logic.
To get the most out of this book
In order to test and run the code examples you will need a working instance of PostgreSQL 11 or greater. In particular, it is required that you have a working client (such as psql), a user account, and a database you can work on.
In order to test Perl examples you will need a recent installation of Perl 5, and a Java 8 JDK to compile and run Java examples.
Any other specific requirements will be detailed in the chapters.
Download the example code files
You can download the example code files for this book from your account at www.packt.com. If you purchased this book elsewhere, you can visit www.packt.com/support and register to have the files emailed directly to you.
You can download the code files by following these steps:
Log in or register at
www.packt.com
.
Select the
SUPPORT
tab.
Click on
Code Downloads & Errata
.
Enter the name of the book in the
Search
box and follow the onscreen instructions.
Once the file is downloaded, please make sure that you unzip or extract the folder using the latest version of:
WinRAR/7-Zip for Windows
Zipeg/iZip/UnRarX for Mac
7-Zip/PeaZip for Linux
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/PostgreSQL-11-Quick-Start-Guide. In case there's an update to the code, it will be updated on the existing GitHub repository.
We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
Get in touch
Feedback from our readers is always welcome.
General feedback: If you have questions about any aspect of this book, mention the book title in the subject of your message and email us at [email protected].
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packt.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the Internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Reviews
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packt.com.
PostgreSQL Server-side Programming
The motto of PostgreSQL is widely known: the most advanced open source database in the world. PostgreSQL is a rock-solid, scalable, and safe enterprise-level relational database that is gaining increasing popularity thanks to its wide variety of features and its stability. It is developed and maintained by a team of database experts, but it is open source, which means it is not a commercial product; it belongs to everyone and everyone can contribute to it. Moreover, thanks to its permissive BSD-style license, it can be released as a custom product, allowing both the marketplace and business opportunities to grow.
The latest release of this database is PostgreSQL 11. This version includes a number of new features on both the core side, such as replication and partitioning, and the in-database development side, such as procedures and improved support for event triggers. Developing within PostgreSQL is fun and easy, as it provides a rich infrastructure for developers to integrate the business logic within the database itself. We can implement this logic in a large set of available languages, including Perl, Python, Java, and Ruby, breaking the restriction of having to carry out all database-related activity in SQL.
This book focuses on the development side of interacting with PostgreSQL, which means embedding the code into the database in order to automate tasks, keep data more coherent by enforcing rules, mangling data, and transforming it. Throughout this book, we will look at two "external" languages: Perl and Java. Choosing which external languages to use was not easy, since PostgreSQL supports a large number of them, but the important concept is that, you, the developer, are free to choose the language you prefer in order to implement server-side programming with PostgreSQL. Of course, as you can imagine and as we will see over the course of the book, this does not mean that any language is appropriate for any task. Languages behave differently because they have different sets of features, different cultures, different ecosystems and libraries, and different support for different tools. Therefore, even though PostgreSQL allows us freedom with regard to the language we use, it is important that we bear in mind that different situations might require different languages.
Some examples will be implemented using the C language, which is the language that PostgreSQL itself is implemented in. For this reason, it has better support in PostgreSQL. However, it is possible to almost totally avoid developing in the C language and to opt instead for friendlier and easier languages.
In this chapter, we will take a look at the following topics:
What server-side programming is
An introduction to the languages that will be used in the rest of the book to implement examples
How the book is organized and which topics will be covered in each chapter
How to read and understand the code examples
What is server-side programming?
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.
How to get help
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.
The example database
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
photo
s 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.
The source code of the examples in this book
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.
Summary
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.
Statement Tricks: UPSERTs, RETURNING, and CTEs
Before taking a closer look at server-side programming, it is worth exploring some of the cool features PostgreSQL provides that allow us to enhance statements. Sometimes, developers start working in a rush to solve a specific problem, which often means writing a function, a routine, or a whole program just to inspect some data. The features provided by PostgreSQL alleviate the need to do this.
This chapter will look at some of these features, and will offer hints and tricks so that you can gain as much as possible from ordinary statements. This will not only make any problems simpler to address, but will also improve your database experience.
In particular, in day-to-day database activity, there is often the need to get back auto-generate values (for example, keys, dates, timestamps, and so on), fix insertion conflicts, and even recursing on a flat data set.
So, after reading this chapter, we will have learned the following:
How to solve tuple insertion conflicts
How to get back automatically generated tuple data, such as incremental keys or timestamps
How to write better statements with Common Table Expressions
How to perform recursion at a statement level using Recursive Common Table Expressions
Getting back modified data with RETURNING
Each write-statement (INSERT, UPDATE, or DELETE) supports an optional RETURNING predicate that makes the statement return a results set with the manipulated tuples. From a conceptual point of view, it is as if these INSERT, UPDATE, and DELETE commands are immediately followed by an automatic SELECT statement.
This feature is very useful. It allows us to get back the exact values of the manipulated tuples, such as an automatically generated key, a computed timestamp, or other non-predictable values. It also allows us to pipeline write statements one after another, as you will see later in this chapter.
Let's take a look at the RETURNING function in action. Imagine that we need to insert some random data into the files table, as follows:
testdb=>
INSERT
INTO
files
(
f_name, f_hash, f_size
)
SELECT
