Database Reference
In-Depth Information
CHAPTER 7
SQL: The PostgreSQL Way
PostgreSQL already outclasses other database products when it comes to ANSI SQL
compliance. It cements its lead by adding constructs that range from convenient syntax
shorthands to avant-garde features that break the bounds of traditional SQL. In this
chapter, we'll cover some SQL tidbits not often found in other databases. For this chapter,
you should have a working knowledge of SQL; otherwise, you may not appreciate the
labor-saving
amuse-bouche
that PostgreSQL brings to the table.
Views
In a relational database, tables store normalized data. To access these scattered tables of
data, you write queries that join underlying tables. When you find yourself writing the
same query over and over again, consider creating a view. Simply put, a view is nothing
more than a query permanently stored in the database.
Some purists have argued that one should never directly query an underlying table
except via views. This means you'd create a view for every table that you intend to query
directly. The benefit is the added layer of indirection useful for controlling permissions
and abstraction of logic. We find this to be sound advice, but laziness gets the better of
us.
Views have evolved over the years. Prior to version 9.1, the only way to update data in
a view was to use rules. You can see an example in
Database Abstraction with Updatable
Views
. Although you can still use rules to update view data, the preferred way is to use
INSTEAD OF
triggers. The trigger approach complies with standards and is what you'll
find in other database products.
Version 9.3 unveiled automatically updatable views. If your view draws from a single
table and you include the primary key as an output column, you can issue an
UPDATE
command directly against your view. The underlying table will store the update.
