PostgreSQL: Up and Running, 2nd Edition

Chapter 8. Writing Functions

With most databases, you can string a series of SQL statements together and treat them as a unit. PostgreSQL is no exception. Different databases ascribe different names for this unit: stored procedures, user-defined functions, and so on. PostgreSQL simply refers to them as functions.

Aside from marshalling SQL statements, functions often add the capability to control the execution of the SQL using procedural languages (PLs). In PostgreSQL, you have your choice of languages when it comes to writing functions. SQL, C, PL/pgSQL, PL/Perl, and PL/Python are often packaged with installers. As of version 9.2, you’ll also find PL/V8, which allows you to write procedural functions in JavaScript. PL/V8 should be an exciting addition for web developers and a darling companion to the built-in json and jsonb data types covered in JSON.

You can always install additional languages such as PL/R, PL/Java, PL/sh, PL/TSQL, and even experimental ones geared for high-end processing and artificial intelligence, such as PL/Scheme or PL/OpenCL. You can find a listing of available languages in Procedural Languages.

Anatomy of PostgreSQL Functions

Function Basics

Regardless of which languages you choose for writing functions, all functions share a similar structure, shown in Example 8-1.

Example 8-1. Basic function structure

CREATE OR REPLACE FUNCTION func_name(arg1 arg1_datatype DEFAULT arg1_default)
RETURNS some type | set of some type | TABLE (..) AS
$$
BODY of function $$ LANGUAGE ...