Book Image

PostgreSQL Server Programming - Second Edition

Book Image

PostgreSQL Server Programming - Second Edition

Overview of this book

Related Content you might be interested in

Current Title:

Small book image
PostgreSQL Server Programming - Second Edition
Feb, 2015 | 320 pages
No titles found
Table of Contents (21 chapters)
PostgreSQL Server Programming Second Edition
PostgreSQL Server Programming Second Edition
Credits
Credits
About the Authors
About the Authors
About the Reviewers
About the Reviewers
www.PacktPub.com
www.PacktPub.com
Preface
Preface
Free Chapter
1
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
Data cleaning
Custom sort orders
Programming best practices
Caching
Wrapping up – why program in the server?
Summary
2
Server Programming Environments
Server Programming Environments
Cost of acquisition
Availability of developers
Licensing
Predictability
Community
Procedural languages
Summary
3
Your First PL/pgSQL Function
Your First PL/pgSQL Function
Why PL/pgSQL?
The structure of a PL/pgSQL function
Conditional expressions
Returning a record
Acting on the function's results
Summary
4
Returning Structured Data
Returning Structured Data
Sets and arrays
Returning sets
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
Summary
5
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
Visibility
Variables passed to the PL/pgSQL TRIGGER function
Summary
6
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
Summary
7
Debugging PL/pgSQL
Debugging PL/pgSQL
Manual debugging with RAISE NOTICE
Visual debugging
Summary
8
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?
Why untrusted?
Quick introduction to PL/Python
Thinking out of the "SQL database server" box
Summary
9
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
Summary
10
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
Summary
11
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
Untrusted Perl
Summary
12
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
Untrusted Tcl
Summary
13
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
Summary
14
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
Summary
Index
Index

Accessing databases

PL/Tcl functions provide you with SPI functions to access the database and run DML/DDL statements.

The functions are the following:

  • spi_exec: This executes a SQL statement

  • spi_prepare: This prepares a SQL statement

  • spi_execp: This executes a prepared statement

The spi_exec function has the following syntax:

spi_exec ?-count n? ?-array name? command ?loop-body?

The spi_exec function runs a SQL statement, and it takes some optional parameters, as follows:

  • -count: This parameter allows you to specify the maximum number of rows processed by the command. If you provide the value 3, only 3 rows will be processed. This is similar to specifying FETCH [n] in a cursor.

  • -array: If this parameter is specified, the column values are stored into a named associative array and the column names are used as array indexes. If this parameter is not specified, the result values are stored in the Tcl variables of the same name.

If there is a loop body specified, then it is treated as a script that is...

Previous Section
End of Section 6
Next Section