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
addition for web developers and a darling companion to the built-in
jsonb data types covered in
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
Regardless of which languages you choose for writing functions, all functions share a similar structure, shown in Example 8-1.
CREATE OR REPLACE FUNCTION
some type | set of some type | TABLE (..)AS $$
BODY of function$$ LANGUAGE ...