Chapter 7. SQL: The PostgreSQL Way

PostgreSQL is one of the most ANSI-SQL compliant databases on the
market. It even supports many of the additions introduced with the SQL:2006+
standard. PostgreSQL goes much further and adds constructs that range from
mundane syntax shorthands to avant-garde features that break the bounds of
traditional SQL. In this chapter, we’ll cover some SQL constructs 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 tidbits
that PostgreSQL brings to the table.

SQL Views

Like most relational databases, PostgreSQL supports views. Some
things have changed over the years on how views work and how you can
update the underlying tables via updates on views. In pre-PostgreSQL 9.1,
views were updatable but required INSTEAD OFUPDATE, DELETErules
on the view. In PostgreSQL 9.1, the preferred way of updating data via a
view is to use INSTEAD OF triggers instead of rules, though
rules are still supported. The trigger approach is standards compliant and
more along the lines of what you’ll find in other databases that support
triggers and updatable views.

Unlike Microsoft SQL Server and MySQL, simple views are not
automatically updatable and require writing an instead-of rule or trigger
to make them updatable. On the plus side, you have great control over how
the underlying tables will be updated. We’ll cover triggers in more detail
in Chapter 8. You can see an example of building ...