O'Reilly logo

PostgreSQL: Up and Running, 2nd Edition by Leo S. Hsu, Regina O. Obe

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required