PostgreSQL: Up and Running

Chapter 8. Writing Functions

As with most databases, you can string a series of SQL statements together and treat them as a unit. Different databases ascribe different names for this unit—stored procedures, modules, macros, prepared statements, and so on. PostgreSQL calls them functions. Aside from simply unifying various SQL statements, these units often add the capability to control the execution of the SQL statements through using procedural language (PL). In PostgreSQL, you have your choice of languages when it comes to writing functions. Often packaged along with binary installers are SQL, C, PL/pgSQL, PL/Perl, PL/Python. In version 9.2, you’ll also find plv8js, which will allow you to write procedural functions in JavaScript. plv8js should be an exciting addition to web developers and a nice companion to the built-in JSON type.

You can always install additional languages such as PL/R, PL/Java, PL/sh, and even experimental ones geared for high-end processing and AI, such as PL/Scheme or PgOpenCL. A list of available languages can be found here:Procedural Languages

Anatomy of PostgreSQL Functions

Function Basics

Regardless which language you choose to write a particular function, they all share a similar structure.

Example 8-1. Basic Function Structure

CREATE OR REPLACE FUNCTION func_name(
	arg1 arg1_datatype)
RETURNS some_type | setof sometype | TABLE (..) AS
$$
BODY of function
$$
LANGUAGE language_of_function

Functional definitions can include additional qualifiers to optimize execution ...