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

Licensing

About 2 months after Oracle bought MySQL, they announced a plan that divided the development into two camps: a MySQL community edition and a professional version. The community edition would no longer gain any new features, and the professional version would become a commercial product.

There was a vast and thunderous sucking sound in the open source community, as they thrashed wildly about to find a new platform for Free and Open Source Software (FOSS) development.

Oracle immediately (in about 2 weeks) countermanded the order and declared that things will stay as they were for the indefinite future. Those with short memories, forgiving hearts, or who just weren't paying attention went on about their business. Many other open source projects either switched to PostgreSQL or suddenly grew PostgreSQL database support.

Today, we have MySQL and MySQL Enterprise Edition. If you want backup, high availability, enterprise scalability, and the MySQL Enterprise Monitor, you now have to pony...