Server-Side Programming

The task of programming for PostgreSQL falls into two broad categories: server-side programming and client-side programming.

Server-side code (as the name implies) is code that executes within a PostgreSQL server. Server-side code executes the same way regardless of which language was used to implement any given client. If the client and server are running on different physical hosts , all server-side code executes on the server machine and within the server process. If the client and server are running on the same machine, server-side code still runs within the server process. In most cases, server-side code is written in one of the procedural languages distributed with PostgreSQL.

PostgreSQL version 7.1 ships with three procedural languages: PL/pgSQL, PL/Tcl, and PL/Perl. Release 7.2 adds PL/Python to the mix.

You can use procedural languages to create functions that execute within the server. A function is a named sequence of statements that you can use within an SQL expression. When you write a function in a server-side language, you are extending the server. These server extensions are also known as stored procedures .

PL/pgSQL

If you have ever used a commercial database system ”Oracle, Sybase, or SQL Server, for example ”you have probably used a SQL-based procedural language. Oracle's procedural language is called PL/SQL; Sybase and SQL Server use TransactSQL. PL/pgSQL is very similar to these procedural languages.

PL/pgSQL combines the declarative nature of SQL commands with structures offered by other languages. When you create a PL/pgSQL function, you can declare local variables to store intermediate results. PL/pgSQL offers a variety of loop constructs ( FOR loops , WHILE loops, and cursor iteration loops). PL/pgSQL gives you the capability to conditionally execute sections of code based on the results of a test. You can pass parameters to a PL/pgSQL function, making the function reusable. You can also invoke other functions from within a PL/pgSQL function.

Chapter 7, "PL/pgSQL," provides an in-depth description of PL/pgSQL.

Other Procedural Languages Supported by PostgreSQL

One of the more unusual aspects of PostgreSQL (compared to other database systems) is that you can write procedural code in more than one language. As noted previously, the standard distribution of PostgreSQL includes PL/pgSQL, PL/Perl, PL/Tcl, and, as of release 7.2, PL/Python.

The latter three languages each enable you to create stored procedures using a subset of the host language. PostgreSQL restricts each to a subset of the language to ensure that a stored procedure can't do nasty things to your environment.

Specifically, the PostgreSQL procedural languages are not allowed to perform I/O external to the database (in other words, you can't use a PostgreSQL procedural language to do anything outside of the context of the server). If you find that you need to affect your external environment, you can load an untrusted procedural language, but be aware that you will be introducing a security risk when you do so.

Because of space limitations, I won't be discussing procedural languages other than PL/pgSQL in this book. If you want to explore PL/Perl, PL/Tcl, or PL/Python, I would recommend that you find a good book about the base language and consult the PostgreSQL reference documentation for PostgreSQL-specific information.

When you install PostgreSQL from a standard distribution, none of the server-side languages are installed. You can pick and choose which languages you want to install in the server. If you don't use a given language, you can choose not to install it. I'll show you how to install server-side languages in Chapter 7.

You can see which languages are currently installed in your database server with the following query:

 movies=# select * from pg_language;  lanname   lanispl  lanpltrusted  lanplcallfoid  lancompiler ----------+---------+--------------+---------------+-------------  internal  f        f                         0  n/a  C         f        f                         0  /bin/cc  sql       f        f                         0  postgres (3 rows) 

You can see that my server currently supports three languages: internal , C , and sql . The lanispl column tells us that none of these are considered to be procedural languages. You may be thinking that C should be considered a procedural language, but in this context a procedural language is one that can be installed and de-installed from the server. You can determine whether a language is trusted by examining the lanpltrusted column. A trusted language promises not to provide elevated privileges to a user . If a language is not a trusted language, only PostgreSQL superusers can create new function in that language.

Extending PostgreSQL Using External Languages

PostgreSQL-hosted procedural languages are not the only tools available for extending the server. You can also add extensions to a PostgreSQL server by creating custom data types, new functions, and new operators written in an external language (usually C or C++).

When you create procedural-language extensions, the source code (and the object-code, if any) for those functions is stored in tables within the database. When you create a function using an external language, the function is not stored in the database. Instead, it is stored in a shared-library that is linked into the server when first used.

You can find many PostgreSQL extensions on the web. For example, the PostGIS project adds a set of data types and supporting functions for dealing with geographic data. The contrib directory of a PostgreSQL distribution contains an extension for dealing with ISBNs and ISSNs.

In Chapter 6, "Extending PostgreSQL," I'll show you a few simple examples of how to add custom data types and functions written in C.