Database Reference
In-Depth Information
$$
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
();
Defines the trigger function. This function can be used on any table that has a
upd_ts
column. It updates the
upd_ts
field to the current time before returning
the changed record. Trigger functions that change values of a row should be
called only in the
BEFORE
event, because in the
AFTER
event, all updates to the
NEW
record will be ignored.
This is a new feature introduced in version 9.0 that allows us to limit the firing
of the trigger so it happens only if specified columns have changed. Prior to
version 9.0, the trigger would fire on any update and you would need to perform
a column-wise comparison using
OLD.some_column
and
NEW.some_column
to
determine what changed. (This feature is not supported for
INSTEAD OF
triggers.)
Writing PL/Python Functions
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. Since version
9.0, PostgreSQL supports both Python 2 and Python 3.
Although you can install both
plpython2u
and
plpython3u
in the
same database, you can't use both during the same session. This
means that you can't write a query that calls both
plpython2u
and
plpython3u
functions. You may encounter a third extension called
plpythonu
; this is an alias for
plpython2u
and left around for back‐
ward compatibility.)
In order to use PL/Python, you first need to install Python on your server. For Windows
Linux/Unix systems, Python binaries are usually available via the various distributions.
tension:
CREATE
EXTENSION
plpython2u
;
CREATE
EXTENSION
plpython3u
;
Make absolutely sure that you have Python properly running on your server before
attempting to install the extension or else you will run into errors that could be difficult
to troubleshoot.