PostgreSQL: Up and Running, 2nd Edition

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 ...