PostgreSQL Server Programming电子书

PostgreSQL Server Programming

Table of Contents

PostgreSQL Server Programming

Credits

About the Authors

About the Reviewer

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

On caching

Wrap up – why program in the server?

Performance

Ease of maintenance

Simple ways to tighten security

Summary

2. Server Programming Environment

Cost of acquisition

Availability of developers

Licensing

Predictability

Community

Procedural languages

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 (UDF)

Other parameters

More control

Summary

3. Your First PL/pgSQL Function

Why PL/pgSQL?

Structure of a PL/pgSQL function

Accessing function arguments

Conditional expressions

Loops with counters

Looping through query results

PERFORM versus SELECT

Returning a record

Acting on function results

Summary

4. Returning Structured Data

Sets and arrays

Returning sets

Returning a set of integers

Using a set-returning function

Returning rows from a 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

Summary of RETURN SETOF variants

Returning cursors

Iterating over cursors returned from another function

Wrap up of functions returning a cursor(s)

Other ways to work with structured data

Complex data types for 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

Simple "Hey, I'm called" trigger

The audit trigger

Disallowing DELETE

Disallowing TRUNCATE

Modifying the NEW record

Timestamping trigger

Immutable fields trigger

Controlling when a trigger is called

Conditional trigger

Trigger on specific field changes

Visibility

And most importantly – use triggers cautiously!

Variables passed to the PL/pgSQL TRIGGER function

Summary

6. Debugging PL/pgSQL

''Manual'' debugging with RAISE NOTICE

Throwing exceptions

Logging to a file

Advantages of RAISE NOTICE

Disadvantages of RAISE NOTICE

Visual debugging

Getting the debugger installed

Installing pgAdmin3

Using the debugger

Advantages of the debugger

Disadvantages of the debugger

Summary

7. Using Unrestricted Languages

Are untrusted languages inferior to trusted ones?

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() for tracking 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

Summary

8. Writing Advanced Functions in C

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

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

Additional resources for C

Summary

9. Scaling Your Database with PL/Proxy

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

PL/Proxy language syntax

CONNECT, CLUSTER, and RUN ON

SELECT and TARGET

SPLIT – distributing array elements over several partitions

Distribution of data

Configuring PL/Proxy cluster using functions

Configuring PL/Proxy cluster using SQL/MED

Moving data from the single to the partitioned database

Summary

10. Publishing Your Code as PostgreSQL Extensions

When to create an extension

Unpackaged extensions

Extension versions

The .control file

Building an extension

Installing an extension

Publishing your extension

Introduction to the PostgreSQL Extension Network

Signing up to publish your extension

Creating an extension project the easy way

Providing the metadata about the extension

Writing your extension code

Creating the package

Submitting the package to PGXN

Installing an extension from PGXN

Summary

Index