Database Reference
In-Depth Information
ing. The higher you set the cost, the more computation the planner will assume the
function needs.
ROWS
Applies only to functions returning sets of records. This value provides an estimate
of how many rows will be returned. The planner will take this value into consid‐
eration when coming up with the best strategy.
SECURITY DEFINER
This causes execution to take place within the security context of the owner of the
function. If omitted, the function executes under the context of the user calling the
function. This qualifier is useful for giving people rights to update a table via a
function when they do not have direct update privileges.
Triggers and Trigger Functions
No database of merit should lack triggers, which automatically detect and handle
changes in data. PostgreSQL allows you to attach triggers to both tables and views.
Triggers can actuate at both the statement level and the row level. Statement triggers
run once per SQL statement, whereas row triggers run for each row affected by the SQL.
For example, if you execute an
UPDATE
statement that affects 1,500 rows, a statement-
level update trigger will fire only once, whereas the row-level trigger can fire up to 1,500
times.
You can further refine the timing of the trigger by making a distinction between
BE
FORE
,
AFTER
, and
INSTEAD OF
triggers. A
BEFORE
trigger fires prior to the execution of
the statement, giving you a chance to cancel or back up data before the change. An
AFTER
trigger fires after statement execution, giving you a chance to retrieve the new data
values.
AFTER
triggers are often used for logging or replication purposes.
INSTEAD OF
triggers execute in lieu of the statement. You can attach
BEFORE
and
AFTER
triggers only
to tables, and
INSTEAD OF
triggers only to views.
You can also adorn a trigger with a
WHEN
condition to control which rows being updated
will fire the trigger, or an
UPDATE OF
columns_list
clause to have the trigger fire only
if certain columns are updated. To gain a more nuanced understanding of the interplay
between triggers and the underlying statement, see the official documentation:
Over‐
view of Trigger Behavior
. We also demonstrated a view-based trigger in
Example 7-4
.
PostgreSQL offers specialized functions to handle triggers. These are called
trigger func‐
tions
and behave like any other function and have the same basic structure. Where they
differ is in the input parameter and the output type. A trigger function never takes an
argument, because internally the function already has access to the data and can modify
it.
