Database Reference
In-Depth Information
are examples of composite types. If you define your own type, you can define new
functions and operators to work with the type:
div
,
grad
, and
curls
, anyone?
cast
Casts are prescriptions for converting from one data type to another. They are
backed by functions that actually perform the conversion. What is rare about Post‐
greSQL is the ability to create your own casts and thus change the default behavior
of casting. For example, imagine you're converting zip codes (which in the United
States are five digits long) to
character
from
integer
. You can define a custom
cast that automatically prepends a zero when the zip is between 1000 and 9999.
Casting can be
implicit
or
explicit
. Implicit casts are automatic and usually expand
from a more specific to a more generic type. When an implicit cast is not offered,
you must cast explicitly.
sequence
A sequence controls the autoincrementation of a serial data type. PostgresSQL au‐
tomatically creates sequences when you define a serial column, but you can easily
change the initial value, increment, and next value. Because sequences are objects
in their own right, more than one table can use the same sequence object. This
allows you to create a unique key value that can span tables. Both SQL Server and
Oracle have sequence objects, but you must create them manually.
row
or
record
We use the terms
rows
and
records
interchangeably. In PostgreSQL, rows can be
treated independently from their respective tables. This distinction becomes ap‐
parent and useful when you write functions or use the row constructor in SQL.
trigger
You will find triggers in most enterprise-level databases; triggers detect data-change
events. When PostgreSQL fires a trigger, you have the opportunity to execute trigger
functions in response. A trigger can run in response to particular types of statements
or in response to changes to particular rows, and can fire before or after a data-
change event.
Trigger technology is evolving rapidly in PostgreSQL. Starting in version 9.0, a
WITH
clause lets you specify a Boolean
WHEN
condition, which is tested to see whether the
trigger should be fired. Version 9.0 also introduced the
UPDATE OF
clause, which
allows you to specify which column(s) to monitor for changes. When the column
changes, the trigger is fired, as demonstrated in
Example 8-11
. In version 9.1, a data
change in a view can fire a trigger. In version 9.3, data definition language (DDL)
events can fire triggers. The DDL events that can fire triggers are listed in the
Event
Trigger Firing Matrix
.
In version 9.4, triggers for foreign tables were introduced.
See
CREATE TRIGGER
for more details about these options.
