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 .
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
 
Search WWH ::




Custom Search