Database Reference
In-Depth Information
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” on page 96
.
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
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