PostgreSQL Server Programming - Second Edition

Book Image

PostgreSQL Server Programming - Second Edition

Book Image

PostgreSQL Server Programming - Second Edition

Overview of this book

PostgreSQL Server Programming Second Edition

PostgreSQL Server Programming Second Edition

Credits

About the Authors

About the Authors

About the Reviewers

About the Reviewers

www.PacktPub.com

www.PacktPub.com

Preface

Free Chapter

What Is a PostgreSQL Server?

What Is a PostgreSQL Server?

Why program in the server?

About this book's code examples

Moving beyond simple functions

Managing related data with triggers

Auditing changes

Custom sort orders

Programming best practices

Wrapping up – why program in the server?

Server Programming Environments

Server Programming Environments

Cost of acquisition

Availability of developers

Procedural languages

Your First PL/pgSQL Function

Your First PL/pgSQL Function

The structure of a PL/pgSQL function

Conditional expressions

Returning a record

Acting on the function's results

Returning Structured Data

Returning Structured Data

Sets and arrays

Using a set returning function

Functions based on views

OUT parameters and records

A summary of the RETURN SETOF variants

Returning cursors

Other ways to work with structured data

PL/pgSQL Trigger Functions

PL/pgSQL Trigger Functions

Creating the trigger function

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

The audit trigger

Disallowing DELETE

Disallowing TRUNCATE

Modifying the NEW record

The immutable fields trigger

Controlling when a trigger is called

Variables passed to the PL/pgSQL TRIGGER function

PostgreSQL Event Triggers

PostgreSQL Event Triggers

Use cases for creating event triggers

Creating event triggers

Creating an audit trail

Preventing schema changes

A roadmap of event triggers

Debugging PL/pgSQL

Debugging PL/pgSQL

Manual debugging with RAISE NOTICE

Visual debugging

Using Unrestricted Languages

Using Unrestricted Languages

Are untrusted languages inferior to trusted ones?

Can you use untrusted languages for important functions?

Will untrusted languages corrupt the database?

Quick introduction to PL/Python

Thinking out of the "SQL database server" box

Writing Advanced Functions in C

Writing Advanced Functions in C

The simplest C function – return (a + b)

Adding functionality to add(int, int)

Basic guidelines for writing C code

Error reporting from C functions

Running queries and calling PostgreSQL functions

Handling records as arguments or returned values

Fast capturing of database changes

Doing something at commit/rollback

Synchronizing between backends

Writing functions in C++

Additional resources for C

Scaling Your Database with PL/Proxy

Scaling Your Database with PL/Proxy

Creating a simple single-server chat

Dealing with success – splitting tables over multiple databases

Connection Pooling

PL/Perl – Perl Procedural Language

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

PL/Tcl – Tcl Procedural Language

PL/Tcl – Tcl Procedural Language

Installing PL/Tcl

A simple PL/Tcl function

Passing and returning arrays

Passing composite-type arguments

Accessing databases

Writing PL/Tcl triggers

Publishing Your Code as PostgreSQL Extensions

Publishing Your Code as PostgreSQL Extensions

When to create an extension

Unpackaged extensions

Extension versions

The .control file

Building an extension

Installing an extension

Viewing extensions

Publishing your extension

Installing an extension from PGXN

PostgreSQL as an Extensible RDBMS

PostgreSQL as an Extensible RDBMS

What can't be extended?

Creating a new operator

Creating index access methods

Creating user-defined aggregates

Using foreign data wrappers

Index

Customer Reviews

5 star

0

4 star

0

3 star

0

2 star

0

1 star

0

Why PL/pgSQL?

PL/pgSQL is a powerful SQL scripting language, that is heavily influenced by PL/SQL, the stored procedure language distributed with Oracle. It is included in the vast majority of PostgreSQL installations as a standard part of the product, so it usually requires no setup at all to begin.

PL/pgSQL also has a dirty little secret. The PostgreSQL developers don't want you to know that it is a full-fledged SQL development language, capable of doing pretty much anything within the PostgreSQL database.

Why is this a secret? For years, PostgreSQL did not claim to have stored procedures. PL/pgSQL functions were originally designed to return scalar values and were intended for simple mathematical tasks and trivial string manipulations.

Over the years, PL/pgSQL developed a rich set of control structures and gained the ability to be used by triggers, operators, and indexes. In the end, developers were grudgingly forced to admit that they had a complete, stored procedure development system...