Writing SQL functions^[2] is fast and easy. Take your existing SQL statements, add a functional header and footer, and you’re done. The ease does mean you’ll sacrifice flexibility. You won’t have fancy control languages to create conditional execution branches. More restrictively, you can’t run dynamic SQL statements that you piece together based on parameters as you can in most other languages. On the positive side, SQL functions are often inlined by the query planner into the overall plan of a query since the planner can peek into the function. Functions in other languages are always treated as blackboxes. Inlining allows SQL functions to take advantage of indexes and collapse repetitive computations.

Your basic scalar returning SQL function is shown in Example 8-2:

CREATE OR REPLACE FUNCTION ins_logs(param_user_name varchar, param_description text) 
 RETURNS integer AS
$$ INSERT INTO logs(user_name, description) VALUES($1, $2)
RETURNING log_id; $$
LANGUAGE 'sql' VOLATILE;

To call function Example 8-2, we would execute:

SELECT ins_logs('lhsu', 'this is a test') As new_id;

Similarly, you can update data with an SQL function and return a scalar or void as shown in Example 8-3.

CREATE OR REPLACE FUNCTION upd_logs(log_id integer, param_user_name varchar, param_description text) 
 RETURNS void AS
$$ UPDATE logs SET user_name = $2, description = $3, log_ts = CURRENT_TIMESTAMP 
 WHERE log_id = $1;$$
LANGUAGE 'sql' VOLATILE;

SELECT upd_logs(12,'robe', 'Change to regina'),

Functions in almost all languages can return sets. SQL functions can also return sets. There are three common approaches of doing this, using ANSI-SQL standard RETURNS TABLE syntax, using OUT parameters, or returning a composite data type. The RETURNS TABLE approach requires PostgreSQL 8.3 or above, but is closer to what you’ll see in other relational databases. In Example 8-4, we demonstrate how to write the same function in three different ways.

CREATE FUNCTION sel_logs_rt(param_user_name varchar)
RETURNS TABLE (log_id int, user_name varchar(50), description text, log_ts timestamptz) AS
$$
SELECT log_id, user_name, description, log_ts FROM logs WHERE user_name = $1;
$$
LANGUAGE 'sql' STABLE;

CREATE FUNCTION sel_logs_out(param_user_name varchar, OUT log_id int
 , OUT user_name varchar, OUT description text, OUT log_ts timestamptz)
RETURNS SETOF record AS
$$
SELECT * FROM logs WHERE user_name = $1;
$$
LANGUAGE 'sql' STABLE;

CREATE FUNCTION sel_logs_so(param_user_name varchar)
RETURNS SETOF logs AS
$$
SELECT * FROM logs WHERE user_name = $1;
$$
LANGUAGE 'sql' STABLE;

All functions in Example 8-4 can be called using:

SELECT * FROM sel_logs_rt('lhsu'),

When your functional needs exceed reaches beyond SQL, PL/pgSQL is the most common option. PL/pgSQL stands apart from SQL in that you can declare local variables using DECLARE, you can have control flow, and the body of the function needs be enclosed in a BEGIN..END block. To demonstrate the difference, we have rewritten Example 8-4 as a PL/pgSQL function.

CREATE FUNCTION sel_logs_rt(param_user_name varchar)
RETURNS TABLE (log_id int, user_name varchar(50), description text, log_ts timestamptz) AS
$$
BEGIN
	RETURN QUERY
    SELECT log_id, user_name, description, log_ts 
      FROM logs WHERE user_name = param_user_name;
END;
$$
LANGUAGE 'plpgsql' STABLE;

Python is a slick language with a vast number of available libraries. PostgreSQL is the only database we know of that’ll let you compose functions using Python. PostgreSQL 9.0+ supports both Python 2 and Python 3.

In order to use PL/Python, you first need to install Python on your server. For Windows and Mac OS, Python installers are available at http://www.python.org/download/. For Linux/Unix systems, Python binaries are usually available via the various distros. For details, refer to PL/Python. After you have Python on your server, proceed to install the PostgreSQL Python extension using the commands below:

CREATE EXTENSION plpython2u;
CREATE EXTENSION plpython3u;

(You will find a third extension called plpythonu, which is an alias for plpython2u and intended for backwards compatibility.) Make sure you have Python properly running on your server before attempting to install the extension or else you will run into errors.

You should install a minor version of Python that matches what your plpythonu extensions were compiled against. For example, if your plpython2u is compiled against 2.7, then you’ll need to install Python 2.7.

CREATE OR REPLACE FUNCTION postgresql_help_search(param_search text)
RETURNS text AS 
$$
import urllib, re  
response = urllib.urlopen('http://www.postgresql.org/search/?u=%2Fdocs%2Fcurrent%2F&q=' + param_search)   
raw_html = response.read() 
result = raw_html[raw_html.find("<!-- docbot goes here -->"):raw_html.find("<!-- pgContentWrap -->") - 1]   
result = re.sub('<[^<]+?>', '', result).strip()  
return result 
$$
LANGUAGE plpython2u SECURITY DEFINER STABLE;

SELECT search_term, left(postgresql_help_search(search_term), 125) As result FROM (VALUES ('regexp_match'), ('pg_trgm'), ('tsvector')) As X(search_term);

search_term  |  result
regexp_match  |  Results 1-7 of 7.
    1. PostgreSQL: Documentation: Manuals: Pattern Matching [1.46]
    ...matching a POSIX regular
pg_trgm|Results 1-8 of 8.


    1. PostgreSQL: Documentation: Manuals: pg_trgm [0.66]
    ...pg_trgm
The
pg_trgm
module provide
tsvector  |  Results 1-20 of 32.
   Result pages: 1 2 Next
    1. PostgreSQL: Documentation: Manuals: Text Search Functions
(3 rows)

CREATE OR REPLACE FUNCTION list_incoming_files()
RETURNS SETOF text AS
$$
import os
return os.listdir('/incoming')
$$
LANGUAGE 'plpython2u' VOLATILE SECURITY DEFINER;

SELECT filename FROM list_incoming_files() As filename WHERE filename ILIKE '%.csv'

No database of merit should be without triggers to automatically detect and handle changes in data. Triggers can be added to both tables and views. PostgreSQL offers both statement-level triggers and row-level triggers. Statement triggers run once per statement, while row triggers run for each row called. For instance, suppose we execute an UPDATE command that affects 1,500 rows. A statement-level trigger will fire only once, whereas the row-level trigger can fire up to 1,500 times. More distinction is made between a BEFORE, AFTER, and INSTEAD OF trigger. A BEFORE trigger fires prior to the execution of the command giving you a chance to cancel and change data before it changes data. An AFTER trigger fires afterwards giving you a chance to retrieve revised data values. AFTER triggers are often used for logging or replication purposes. The INSTEAD OF triggers run instead of the normal action. INSTEAD OF triggers can only be used with views. BEFORE and AFTER triggers can only be used with tables. To gain a better understanding of the interplay between triggers and the underlying command, we refer you to the official documentation Overview of Trigger Behavior. We demonstrated an example of a view trigger in Example 7-2.

PostgreSQL offers specialized functions to handle triggers. These trigger functions act just like any other function and have the same basic structure as your standard function. Where they differ are in the input parameter and the output type. A trigger function never takes a literal input argument though internally they have access to the trigger state data and can modify it. They always output a datatype called a trigger. Because PostgreSQL trigger functions are just another function, you can reuse the same trigger function across different triggers. This is usually not the case for other databases where each trigger has its own non-reusable handler code. Each trigger must have exactly one associated triggering function. To apply multiple triggering functions, you must create multiple triggers against the same event. The alphabetical order of the trigger name determines the order of firing and each trigger passes the revised trigger state data to the next trigger in the list.

You can use almost any language to write trigger functions, with SQL being the notable exception. Our example below uses PL/pgSQL, which is by far the most common language for writing triggers. You will see that we take two steps: First, we write the trigger function. Next, we attach the trigger function to the appropriate trigger, a powerful extra step that decouples triggers from trigger functions.

CREATE OR REPLACE FUNCTION trig_time_stamper() RETURNS trigger AS 
$$
BEGIN
    NEW.upd_ts := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER trig_1
BEFORE INSERT OR UPDATE  OF session_state, session_id 
 ON web_sessions 
 FOR EACH ROW
 EXECUTE PROCEDURE trig_time_stamper();

Aggregates are another type of specialized function offered up by PostgreSQL. In many other databases, you’re limited to ANSI-SQL aggregate functions such as MIN(), MAX(), AVG(), SUM(), and COUNT(). You can define your own aggregates in PostgreSQL. Don’t forget that any aggregate function in PostgreSQL can be used as a window function. Altogether this makes PostgreSQL the most customizable database in existence today.

You can write aggregates in almost any language, SQL included. An aggregate is generally composed of one or more functions. It must have at least a state transition function to perform the computation and optional functions to manage initial and final states. You can use a different language for each of the functions should you choose. We have various examples of building aggregates using PL/pgSQL, PL/Python, and SQL in PostgreSQL Aggregates.

Regardless of which languages you code the aggregate, the glue that brings them all together looks the same for all and is of the form:

CREATE AGGREGATE myagg(datatype_of_input)
(SFUNC=state_function_name, STYPE=state_type, FINALFUNC=final_func_name, INITCOND=optional_init_state_value);

The final function is optional, but if specified must take as input the result of the state function. The state function always takes as input the datatype_of_input and result of last state function call. The initial condition is also optional. When present, it is used to initialize the state value. Aggregates can be multi-column as well, as we describe in How to Create Multi-Column Aggregates.

Although SQL functions are the simplest of functions to write, you can still go pretty far with them. In this section, we’ll demonstrate how to create a geometric mean aggregate function with SQL. A geometric mean is the nth root of a product of n positive numbers ((x1*x2*x3...Xn)^(1/n)). It has various uses in finance, economics, and statistics. A geometric mean may have more meaning than an arithmetic mean when the numbers are of vastly different scales. A more suitable computational formula uses logarithm to convert a multiplicative process to an additive one (EXP(SUM(LN(x))/n)). We’ll be using this method in our example.

For our geometric mean aggregate, we’ll use two functions: a state function to add the logs and a final exponential function to convert the logs back. We will also specify an initial condition of zero when we put everything together.

CREATE OR REPLACE FUNCTION geom_mean_state(prev numeric[2], next numeric)
RETURNS numeric[2] AS
$$
SELECT CASE WHEN $2 IS NULL or $2 = 0 THEN $1 
  ELSE ARRAY[COALESCE($1[1],0) + ln($2), $1[2] + 1] END;
$$
LANGUAGE sql IMMUTABLE;

Our transition state function, as shown in Example 8-10, takes two inputs: the previous state passed in as a one-dimensional array with two elements and also the next element in the aggregation process. If the next element is NULL or zero, the state function returns the prior state. Otherwise, it’ll return an array where the first element is the logarithmic sum and the second being the current count. We will need a final function that takes the sum from the state transition and divides by the count.

CREATE OR REPLACE FUNCTION geom_mean_final(numeric[2])
RETURNS numeric AS
$$
SELECT CASE WHEN $1[2] > 0 THEN exp($1[1]/$1[2]) ELSE 0 END;
$$
LANGUAGE sql IMMUTABLE;

Now we stitch all the pieces together in our aggregate definition. Note that our aggregate has an initial condition that is the same as what is returned by our state function.

CREATE AGGREGATE geom_mean(numeric) (SFUNC=geom_mean_state, STYPE=numeric[]
 , FINALFUNC=geom_mean_final, INITCOND='{0,0}'),

Let’s take our geom_mean() function for a test drive. We’re going to compute a heuristic rating for racial diversity and list the top five most racially diverse counties in Massachusetts.

SELECT left(tract_id,5) As county, geom_mean(val) As div_county 
FROM census.vw_facts 
WHERE category = 'Population' AND short_name != 'white_alone'
GROUP BY county
ORDER BY div_county DESC LIMIT 5;

county |     div_county
-------+---------------------
25025  | 85.1549046212833364
25013  | 79.5972921427888918
25017  | 74.7697097102419689
25021  | 73.8824162064128504
25027  | 73.5955049035237656

Let’s put things into overdrive and try our new aggregate function as a window aggregate.

WITH X AS (SELECT tract_id, left(tract_id,5) As county
    , geom_mean(val) OVER(PARTITION BY tract_id) As div_tract
    , ROW_NUMBER() OVER(PARTITION BY tract_id) As rn
    , geom_mean(val) OVER(PARTITION BY left(tract_id,5)) As div_county 
  FROM census.vw_facts WHERE category = 'Population' AND short_name != 'white_alone')
SELECT tract_id, county, div_tract, div_county 
 FROM X 
 WHERE rn = 1 
 ORDER BY div_tract DESC, div_county DESC LIMIT 5;

tract_id    | county |      div_tract       |     div_county
------------+--------+----------------------+---------------------
25025160101 | 25025  | 302.6815688785928786 | 85.1549046212833364
25027731900 | 25027  | 265.6136902148147729 | 73.5955049035237656
25021416200 | 25021  | 261.9351057509603296 | 73.8824162064128504
25025130406 | 25025  | 260.3241378371627137 | 85.1549046212833364
25017342500 | 25017  | 257.4671462282508267 | 74.7697097102419689