Database Reference
In-Depth Information
Chapter 3. Your
First
PL/pgSQL
Function
A function is the basic building block for extending PostgreSQL. A function accepts
input in the form of parameters, and can create output in the form of output paramet-
ers or return values. Many functions are provided by PostgreSQL itself such as the
common mathematical functions, for example, square root and absolute value. For a
comprehensive list of what is already available, go to
http://www.postgresql.org/docs/
The functions that you create have all of the same privileges and power that the built-
in functions possess. The developers of PostgreSQL use the same libraries to extend
the database that you use as a developer to write your business logic.
This means that you have the tools available to be a first class citizen of the Post-
greSQL development community. In fact, there are no second-class seats on this bus.
A function accepts parameters that can be of any data type available in PostgreSQL
and it returns results to the caller using any of the same types. What you do within the
function is entirely up to you. You have been enabled to do anything that PostgreSQL
is capable of doing. You are herewith also warned that you are capable of doing any-
thing that PostgreSQL is capable of doing. The training wheels are off.
In this chapter, you will learn:
• The basic building blocks of a PostgreSQL function
• Passing parameters into a function
• Basic control structures inside of a function
• Returning results out of a function
Why PL/pgSQL?
PL/pgSQL is a powerful SQL scripting language heavily influenced by PL/SQL, the
stored procedure language distributed with Oracle. It is included in the vast majority
of PostgreSQL installations as a standard part of the product, so it usually requires no
setup at all to begin.
