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
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
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
some_type | setof sometype | TABLE (..)AS $$
BODY of function$$ LANGUAGE
Functional definitions can include additional qualifiers to optimize execution ...