In this chapter, the rest of the PostgreSQL building blocks, including views, indexes, functions, triggers, and rules, will be introduced. In addition to that, the web car portal schema will be revised. Several DDL commands, such as CREATE
and ALTER
, will also be introduced.
Since the lexical structure and several DML commands have not been introduced as yet, we will try to use very simple DML commands.
A view can be seen as a named query, or as a wrapper around a SELECT
statement. Views are essential building blocks of relational databases from the UML modeling perspective; a view can be thought of as a method for a UML
class. Views share several advantages over procedures, so the following benefits are shared between views and stored procedures. Views can be used for the following purposes:
Unlike stored procedures, the views dependency tree is maintained in the database; thus, altering a view might be forbidden due to a cascading effect.
It is essential to know the way in which views are designed. The following is some general advice to keep in mind for handling views properly:
row_number
function.In PostgreSQL, the view is internally modeled as a table with a _RETURN
rule. So, the following two pieces of code are equivalent:
CREATE VIEW test AS SELECT 1 AS one; CREATE TABLE test (one INTEGER); CREATE RULE "_RETURN" AS ON SELECT TO test DO INSTEAD SELECT 1;
The preceding example is for the purpose of explanation only, it is not recommended to play with the PostgreSQL catalogue, including the reserved rules, manually. Moreover, note that a table can be converted to a view but not vice versa.
When one creates views, the created tables are used to maintain the dependency between the created views. So when executing the following query:
SELECT * FROM test;
We actually execute the following:
SELECT * FROM(SELECT 1) AS test;
To understand views dependency, let us build a view using another view, as follows:
--date_trunc function is similar to trunc function for numbers, CREATE VIEW day_only AS SELECT date_trunc('day', now()) AS day; CREATE VIEW month_only AS SELECT date_trunc('month', day_only.day)AS month FROM day_only;
The preceding views, month_only
and day_only
, are truncating the time to day and month respectively. The month_only
view depends on the day_only
view. In order to drop the day_only
view, one can use one of the following options:
month_only
view followed by the day_only
view:car_portal=# DROP VIEW day_only; ERROR: cannot drop view day_only because other objects depend on it DETAIL: view month_only depends on view day_only HINT: Use DROP ... CASCADE to drop the dependent objects too. car_portal=# DROP VIEW month_only; DROP VIEW car_portal=# DROP VIEW day_only; DROP VIEW
CASCADE
option when dropping the view:car_portal=# DROP VIEW day_only CASCADE; NOTICE: drop cascades to view month_only DROP VIEW
In the view synopsis shown next, the CREATE
keyword is used to create a view, while the REPLACE
keyword is used to redefine the view if it already exists. The view attribute names can be given explicitly, or they can be inherited from the SELECT
statement:
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ] [ WITH ( view_option_name [= view_option_value] [, ... ] ) ] AS query [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
The following example shows how to create a view that lists only the user information without the password. This might be useful for implementing data authorization to restrict the applications for accessing the password. Note that the view column names are inherited from the SELECT
list, as shown by the d
in the account_information
meta command:
CREATE VIEW account_information AS SELECT account_id, first_name, last_name, email FROM account; car_portal=# d account_information View "public.account_information" Column | Type | Modifiers ------------+---------+----------- account_id | integer | first_name | text | last_name | text | email | text |
The view account information column names can be assigned explicitly as shown in the following example. This might be useful when one needs to change the view column names:
CREATE VIEW account_information (account_id,first_name,last_name,email) AS SELECT account_id, first_name, last_name, email FROM account;
When replacing the view definition using the REPLACE
keyword, the column list should be identical before and after the replacement, including the column type, name, and order. The following example shows what happens when trying to change the view column order:
car_portal=# CREATE OR REPLACE VIEW account_information AS SELECT account_id, last_name, first_name, email FROM account; ERROR: cannot change name of view column "first_name" to "last_name"
Views in PostgreSQL can be categorized in one of the following categories on the basis of their usage:
TEMPORARY
or TEMP
keywords are not used, then the life cycle of the view starts with view creation and ends with the action of dropping it.INSERT
, UPDATE
and DELETE
on views similar to tables. Updatable views can help in bridging the gap between an object model and a relational model to some extent, and they can help in overcoming problems like polymorphism.Since recursion will be covered in the following chapters, we will focus here on the updatable and materialized views.
The materialized view synopsis differs a little bit from the normal view synopsis. Materialized views are a PostgreSQL extension, but several databases, such as Oracle, support it. As shown in the following synopsis below, a materialized view can be created in a certain TABLESPACE
, which is logical since materialized views are physical objects:
CREATE MATERIALIZED VIEW table_name [ (column_name [, ...] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ] AS query [ WITH [ NO ] DATA ]
At the time of creation of a materialized view, it can be populated with data or left empty. If it is not populated, retrieving data from the unpopulated materialized view will raise an ERROR
. The REFRESH MATERIALIZED VIEW
statement can be used to populate a materialized view. The following example shows an attempt to retrieve data from an unpopulated materialized view:
car_portal=# CREATE MATERIALIZED VIEW test_mat_view AS SELECT 1 WITH NO DATA; car_portal=# SELECT * FROM test_mat_view; ERROR: materialized view "test_mat_view" has not been populated HINT: Use the REFRESH MATERIALIZED VIEW command. car_portal=# REFRESH MATERIALIZED VIEW test_mat_view; REFRESH MATERIALIZED VIEW car_portal=# SELECT * FROM test_mat_view; ?column? ---------- 1 (1 row)
Materialized views are often used with data warehousing. In data warehousing, several queries are required for business analyses and for decision support. The data in this kind of applications does not usually change, but the calculation and aggregation of this data is often a costly operation. In general, a materialized view can be used for the following:
Since materialized views are tables, they also can be indexed, leading to a great performance boost.
In our web car portal application, let us assume that we need to create a monthly summary report for the previous month to see which car was searched for the most. Let us also assume that the user search key has the following pattern:
Keyword1=value1&keyword2&value2& … & keywordn=valuen
For example, a search key could be :brand=opel&manufacturing_date=2013
. The summary report can be created using the following query:
CREATE MATERIALIZED VIEW account_search_history AS SELECT Key, count(*) FROM (SELECT regexp_split_to_table(search_key, '&') AS key FROM account_history WHERE search_date >= date_trunc('month', now()-INTERVAL '1 month') AND search_date < date_trunc('month', now())) AS FOO GROUP BY key; car_portal=# SELECT * FROM account_search_history; key | count ------------+------- brand=opel | 2 model=2013 | 1 (2 rows)
The preceding query requires a lot of calculation because of the aggregation function, and also due to the big amount of data in the table. In our car web portal, if we have a million searches per month, that would mean a million records in the database. The query uses the following functions and predicates:
For completion purposes in our example, the generation of the materialized views should be automated using cron job in Linux or the job scheduler software. Additionally, the names of the generated materialized views should be suffixed with the month, since we need to generate a summary table for the search keys each month.
By default, simple PostgreSQL views are auto-updatable. Auto-updatable means that one could use the view with the DELETE, INSERT and UPDATE statements to manipulate the data of the underlying table. If the view is not updatable (which is not simple) due to the violation of one of the following constraints, the trigger and rule systems can be used to make it updatable. The view is automatically updatable if the following conditions are met:
DISTINCT
, WITH
, GROUP BY
, OFFSET
, HAVING
, LIMIT
, UNION
, EXCEPT
, and INTERSECT
.security_barrier
property must not be set.The preceding conditions promise that the view attributes can be mapped directly to the underlying table attributes.
In the web car portal, let us assume that we have a view that shows only the accounts that are not seller accounts. This can be done as follows:
CREATE VIEW user_account AS SELECT account_id, ..first_name, ..last_name, ..email, ..password FROM ..account WHERE ..account_id NOT IN ( ....SELECT account_id FROM seller_account);
The preceding view is updatable by default; thus, one can insert and update the view content as follows:
car_portal=# INSERT INTO user_account VALUES (default, 'test_first_name', 'test_last_name','[email protected]', 'password'); INSERT 0 1 car_portal=# DELETE FROM user_account WHERE first_name = 'test_first_name'; DELETE 1
In the case of an auto-updatable view, one cannot modify the base table if it does not contain data. For example, let us insert an account with a seller account, and then try to delete it:
car_portal=# WITH account_info AS ( INSERT INTO user_account VALUES (default,'test_first_name','test_last_name','[email protected]','password') RETURNING account_id) INSERT INTO seller_account (account_id, street_name, street_number, zip_code, city) SELECT account_id, 'test_street', '555', '555', 'test_city' FROM account_info; INSERT 0 1
car_portal=# DELETE FROM user_account WHERE first_name = 'test_first_name'; DELETE 0
In the preceding example, notice that the insert to the user account was successful, while the DELETE
command did not delete any row.
If one is uncertain whether a view is auto updatable or not, he/she can verify this information using the information schema by checking the value of the
is_insertable_into
flag, as follows:
car_portal-# SELECT table_name, is_insertable_into FROM information_schema.tables WHERE table_name = 'user_account'; table_name | is_insertable_into --------------+-------------------- user_account | YES (1 row)
18.218.153.50