PostgreSQL Server Programming - Second Edition

Usama Dar, Hannu Krosing, Jim Mlodgenski, Kirk Roybal

• Verlag: Packt Publishing
• Jahr: 2015
• ISBN:
  9781783980581
• Thema: Informatica
• Thema: Database

PostgreSQL Server Programming Second Edition

---

Table of Contents

PostgreSQL Server Programming Second Edition

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. What Is a PostgreSQL Server?

Why program in the server?

Using PL/pgSQL for integrity checks

About this book's code examples

Switching to the expanded display

Moving beyond simple functions

Data comparisons using operators

Managing related data with triggers

Auditing changes

Data cleaning

Custom sort orders

Programming best practices

KISS – keep it simple stupid

DRY – don't repeat yourself

YAGNI – you ain't gonna need it

SOA – service-oriented architecture

Type extensibility

Caching

Wrapping up – why program in the server?

Performance

Ease of maintenance

Improved productivity

Simple ways to tighten security

Summary

2. Server Programming Environments

Cost of acquisition

Availability of developers

Licensing

Predictability

Community

Procedural languages

Third-party tools

Platform compatibility

Application design

Databases are considered harmful

Encapsulation

What does PostgreSQL offer?

Data locality

More basics

Transactions

General error reporting and error handling

User-defined functions

Other parameters

More control

Summary

3. Your First PL/pgSQL Function

Why PL/pgSQL?

The structure of a PL/pgSQL function

Accessing function arguments

Conditional expressions

Loops with counters

Statement termination

Looping through query results

PERFORM versus SELECT

Looping Through Arrays

Returning a record

Acting on the function's results

Summary

4. Returning Structured Data

Sets and arrays

Returning sets

Returning a set of integers

Using a set returning function

Functions based on views

OUT parameters and records

OUT parameters

Returning records

Using RETURNS TABLE

Returning with no predefined structure

Returning SETOF ANY

Variadic argument lists

A summary of the RETURN SETOF variants

Returning cursors

Iterating over cursors returned from another function

Wrapping up of functions returning cursors

Other ways to work with structured data

Complex data types for the modern world – XML and JSON

XML data type and returning data as XML from functions

Returning data in the JSON format

Summary

5. PL/pgSQL Trigger Functions

Creating the trigger function

Creating the trigger

Working on a simple "Hey, I'm called" trigger

The audit trigger

Disallowing DELETE

Disallowing TRUNCATE

Modifying the NEW record

The timestamping trigger

The immutable fields trigger

Controlling when a trigger is called

Conditional triggers

Triggers on specific field changes

Visibility

Most importantly – use triggers cautiously!

Variables passed to the PL/pgSQL TRIGGER function

Summary

6. PostgreSQL Event Triggers

Use cases for creating event triggers

Creating event triggers

Creating an audit trail

Preventing schema changes

A roadmap of event triggers

Summary

7. Debugging PL/pgSQL

Manual debugging with RAISE NOTICE

Throwing exceptions

Logging to a file

The advantages of RAISE NOTICE

The disadvantages of RAISE NOTICE

Visual debugging

Installing the debugger

Installing the debugger from the source

Installing pgAdmin3

Using the debugger

The advantages of the debugger

The disadvantages of the debugger

Summary

8. Using Unrestricted Languages

Are untrusted languages inferior to trusted ones?

Can you use untrusted languages for important functions?

Will untrusted languages corrupt the database?

Why untrusted?

Why PL/Python?

Quick introduction to PL/Python

A minimal PL/Python function

Data type conversions

Writing simple functions in PL/Python

A simple function

Functions returning a record

Table functions

Running queries in the database

Running simple queries

Using prepared queries

Caching prepared queries

Writing trigger functions in PL/Python

Exploring the inputs of a trigger

A log trigger

Constructing queries

Handling exceptions

Atomicity in Python

Debugging PL/Python

Using plpy.notice() to track the function's progress

Using assert

Redirecting sys.stdout and sys.stderr

Thinking out of the "SQL database server" box

Generating thumbnails when saving images

Sending an e-mail

Listing directory contents

Summary

9. Writing Advanced Functions in C

The simplest C function – return (a + b)

add_func.c

Version 0 call conventions

Makefile

CREATE FUNCTION add(int, int)

add_func.sql.in

Summary for writing a C function

Adding functionality to add(int, int)

Smart handling of NULL arguments

Working with any number of arguments

Basic guidelines for writing C code

Memory allocation

Use palloc() and pfree()

Zero-fill the structures

Include files

Public symbol names

Error reporting from C functions

"Error" states that are not errors

When are messages sent to the client?

Running queries and calling PostgreSQL functions

A sample C function using SPI

Visibility of data changes

More info on SPI_* functions

Handling records as arguments or returned values

Returning a single tuple of a complex type

Extracting fields from an argument tuple

Constructing a return tuple

Interlude – what is Datum?

Returning a set of records

Fast capturing of database changes

Doing something at commit/rollback

Synchronizing between backends

Writing functions in C++

Additional resources for C

Summary

10. Scaling Your Database with PL/Proxy

Creating a simple single-server chat

Dealing with success – splitting tables over multiple databases

What expansion plans work and when?

Moving to a bigger server

Master-slave replication – moving reads to slave

Multimaster replication

Data partitioning across multiple servers

Splitting the data

PL/Proxy – the partitioning language

Installing PL/Proxy

The PL/Proxy language syntax

CONNECT, CLUSTER, and RUN ON

SELECT and TARGET

SPLIT – distributing array elements over several partitions

The distribution of data

Configuring the PL/Proxy cluster using functions

Configuring the PL/Proxy cluster using SQL/MED

Moving data from the single to the partitioned database

Connection Pooling

Summary

11. PL/Perl – Perl Procedural Language

When to use PL/Perl

Installing PL/Perl

A simple PL/Perl function

Passing and returning non-scalar types

Writing PL/Perl triggers

Untrusted Perl

Summary

12. PL/Tcl – Tcl Procedural Language

Installing PL/Tcl

A simple PL/Tcl function

Null checking with Strict functions

The parameter format

Passing and returning arrays

Passing composite-type arguments

Accessing databases

Writi...