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

Variables passed to the PL/pgSQL TRIGGER function

The following is a complete list of the variables available to a trigger function written in PL/pgSQL:

`OLD`, `NEW`	`RECORD`	This records the before and after images of the row on which the trigger is called. `OLD` is unassigned for `INSERT` and `NEW` is unassigned for `DELETE`. Both are `UNASSIGNED` in statement-level triggers.
`TG_NAME`	`name`	This denotes the name of the trigger (this and following from the trigger definition).
`TG_WHEN`	`text`	`BEFORE`, `AFTER`, or `INSTEAD OF` are the possible values of the variable.
`TG_LEVEL`	`text`	`ROW` or `STATEMENT` are the possible values of the variable.
`TG_OP`	`text`	`INSERT`, `UPDATE`, `DELETE`, or `TRUNCATE` are the possible values of the variable..
`TG_RELID`	`oid`	This denotes the `OID` of the table on which the trigger is created.
`TG_TABLE_NAME`	`name`	This denotes the name of the table (the old spelling `TG_RELNAME` is deprecated but still available).
`TG_TABLE_SCHEMA`	`name`	This denotes the schema name of the table.
`TG_NARGS...`