Views

A view can be considered a named query or a wrapper around a SELECT statement. Views are the essential building blocks of relational databases from a UML modeling perspective; a view can be thought of as a method for a UML class. Views share several advantages with functions; the following benefits are shared between views and stored procedures. Views can be used for the following purposes:

• Simplifying complex queries and increasing code modularity
• Tuning performance by caching the view results for later use
• Decreasing the amount of SQL code
• Bridging the gap between relational databases and object-oriented languages, especially updatable views
• Implementing authorization at the row level, by leaving out rows that do not meet a certain predicate
• Implementing interfaces and the abstraction layer between high-level languages and relational databases
• Implementing last-minute changes

A view should meet the current business needs, instead of potential future business needs. It should be designed to provide certain functionality or service. Note that the more attributes there are in a view, the more effort will be required to refactor the view. In addition to that, when a view aggregates data from many tables and is used as an interface, there might be a degradation in performance, due to many factors (for example, bad execution plans due to outdated statistics for some tables, execution plan time generation, and so on).

When implementing complex business logic in a database using views and stored procedures, database refactoring, especially for base tables, might turn out to be very expensive. To solve this issue, consider migrating the business logic to the application business tier.

Some frameworks, such as object-relational mappers, might have specific needs, such as a unique key. This limits the usage of views in these frameworks; however, we can mitigate these issues by faking the primary keys, via window functions such as row_number.

In PostgreSQL, a view is internally modeled as a table with an _RETURN rule. So, in theory, we can create a table and convert it into a view.

However, this is not a recommended practice. The VIEW dependency tree is well maintained; this means that we cannot drop a view or amend its structure if another view depends on it, as follows:

postgres=# CREATE VIEW test AS SELECT 1 as v;
CREATE VIEW
postgres=# CREATE VIEW test2 AS SELECT v FROM test;
CREATE VIEW
postgres=# CREATE OR REPLACE VIEW test AS SELECT 1 as val;
ERROR: cannot change name of view column "v" to "val"