Writing Functions - PostgreSQL: Up and Running - page 152

Writing PL/pgSQL Functions
When your functional needs outgrow SQL, turning to PL/pgSQL is a common practice.
PL/pgSQL surpasses SQL in that you can declare local variables using DECLARE and you
can incorporate control flow.
Basic PL/pgSQL Function
To demonstrate syntax differences from SQL, in Example 8-10 we rewrite Example 8-4
as a PL/pgSQL function.
Example 8-10. Function to return a table using PL/pgSQL
CREATE FUNCTION select_logs_rt ( param_user_name varchar )
RETURNS TABLE ( log_id int , user_name varchar ( 50 ), description text , log_ts time
stamptz ) AS
$$
BEGIN RETURN QUERY
SELECT log_id , user_name , description , log_ts FROM logs
WHERE user_name = param_user_name ;
END ;
$$
LANGUAGE 'plpgsql' STABLE ;
Writing Trigger Functions in PL/pgSQL
Because you can't write trigger functions in SQL, PL/pgSQL is your next-best bet. In
this section, we'll demonstrate how to write a basic trigger function in PL/pgSQL.
We proceed in two steps. First, we write the trigger function. Second, we explicitly attach
the trigger function to the appropriate trigger. The second step is a powerful feature of
PostgreSQL that decouples the function handling the trigger from the trigger itself. You
can attach the same trigger function to multiple triggers, adding another level of reuse
not found in other databases. Because each trigger function can stand on its own, you
have your choice of languages, and mixing is completely OK. For a single triggering
event, you can set up multiple triggers, each with functions written in a different lan‐
guage. For example, you can have a trigger email a client written in PL/PythonU or PL/
PerlU and another trigger write to a log file with plPgSQL.
A basic trigger function and accompanying trigger is demonstrated in Example 8-11 .
Example 8-11. Trigger function to timestamp new and changed records
CREATE OR REPLACE FUNCTION trig_time_stamper () RETURNS trigger AS
$$
BEGIN
NEW . upd_ts : = CURRENT_TIMESTAMP ;
RETURN NEW ;
END ;