PostgreSQL 11 Server Side Programming Quick Start Guide

Other Books You May Enjoy

References

Summary

Creating the glue code

The Makefile

Defining the input and output functions

Defining the basic type and helper functions

Defining the textual representation

Basic type example – image resolution

Basic types

Where is my composite type?

Type safety

Adding or removing attributes to composite types

Creating a composite type

Composite types

Changing an existing label

Where is my enumeration?

Adding values to an existing enumeration

Type safety

Creating an enumeration

Enumerations

Custom data types

Custom Data Types

References

Summary

Signaling the Background Worker

Deploying and starting the Background Worker

Module Signal Handlers

Background Worker Main Function

The module entry point

An example implementation of a Background Worker

Implementing a Background Worker

Background Workers

An example of IPC between different applications

An example of IPC between two database backends

Channels and event queues

Event special functions

A single process example

Limitations of events

Asynchronous events

Inter-Process Communication (IPC)

Inter Process Communication and Background Workers

References

Summary

The PGXN Client

Getting an extension from the search website

The extension ecosystem

The PostgreSQL Extension Network (PGXN)

Adding an object to an existing extension

Extension data and backups

Installing all the preceding versions

Creating an improved version

Starting from scratch

Creating your own extension

Which extensions are installed?

Creating and removing extensions

Extension management

Relocatable extensions

The script file

The control file

Extensions

Extensions

References

Summary

When to use rules and when to use triggers

Using a rule to route tuples

Using a rule to execute multiple statements– the playlist example

Conditional rules

Where is my rule?

A rule example – archiving tuples on deletion

The syntax of rules

Rules

When and where are rules used by PostgreSQL?

What is the query rewriting system?

Introducing the query rewriting system

Rules and the Query Rewriting System

References

Summary

PL/Java

PL/Perl

Event trigger examples in foreign languages

Managing event triggers

Filtering specific commands

Using event triggers to avoid specific commands

A first event trigger

The trigger function

Data definition triggers

PL/Java Triggers

PL/Perl Triggers

Trigger examples in foreign languages

Using a trigger to perform multiple statements

The REFERENCING clause

The WHEN condition

Aborting the current statement

Parametric triggers

Using triggers to modify data on the fly

Disabling Triggers

Discovering if a table has triggers

Creating a simple trigger

The trigger function

Data manipulation triggers

Triggers

References

Summary

Implementing a PL/Java function with a deployment descriptor

Implementing a PL/Java function without a deployment descriptor

PL/Java main concepts

Installing PL/Java

PL/Java

Implementing routines in PL/Perl

Re-implementing functions in PL/Perl

Function arguments and return types

Database interaction

PL/Perl

PL/Perl and PL/Java

References

Summary

Nesting transactions and procedures

Interacting with transactions

Where is my procedure?

Temporary procedures

Moving procedures to other schemas

Permissions

Security

Returning values to the caller

Procedure argument list

Procedures

Compile and runtime problem detection

A tag insertion function

Permissions

Where is my function?

Temporary functions

Moving functions to other schemas

Costs

Immutability

Security

Returning values to the caller

Function argument list

Return Types

Functions

Supported languages

Using functions procedures and routines

Stored Procedures

References

Summary

The FOUND Global Variable

Throwing away a query result set

Executing dynamic statements

The RAISE statement revisited

Exceptions and error handling

Iterations

Conditionals

Variables and variable assignment

An introduction to PL/pgSQL

The PL/pgSQL Language

References

Summary

Recursive CTEs

Writable CTEs and the RETURNING clause: Pipelining statements

Introducing CTEs

Common Table Expressions (CTEs)

Getting back modified data with RETURNING

Inserting updating or both?

Statement Tricks: UPSERTs RETURNING and CTEs

Summary

The source code of the examples in this book

The example database

How to get help

What is server-side programming?

PostgreSQL Server-side Programming

Reviews

Get in touch

Conventions used

Download the example code files

To get the most out of this book

What this book covers

Who this book is for

Preface

Packt is searching for authors like you

About the reviewers

About the author

Contributors

Packt.com

Why subscribe?

About Packt

Dedication

Title Page

coverpage

coverpage

Title Page

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