Views

A view is the mechanism for examining the data in your database. A view can combine data from multiple tables or can include only certain columns from a single table. Although a view looks like a table, it consists of a selected set of columns or calculations based on those columns from tables in your database. Thus, a view may contain data from columns in more than one table or data that is not actually in any table at all (for example, SELECT COUNT (*) FROM Person).

Features of Views

Following are some of the features of views:

•

You can arrange the columns of a view in any order except that the variable-length column must be last. You can specify only one variable-length column.

•

You can use a restriction clause to specify the set of rows that Pervasive PSQL returns in a view. The restriction clause lists criteria that the data must satisfy to be included in the view. For more information, see the section Restriction Clauses.

•

You can design and customize views for each user and application that accesses the database. You can store these view definitions within the data dictionary for later recall.

•

You can include any number of stored view names in a table list when retrieving, updating, or deleting data unless the view is a read-only view. In a read-only view, you can only retrieve data.

•

In a stored view, you must provide headings for the view’s computed columns and constants and use those names in a list of column names when you retrieve data from the view.

Temporary and Stored Views

You can use SELECT statements to create temporary views or stored views. You use a temporary view only once and then release it. Pervasive PSQL places the definition of a stored view in the data dictionary (X$Proc) so you can recall the view later. You use CREATE VIEW statements to create and name stored views.

Each view name must be unique within a database and cannot exceed 20 characters. For more information about rules for naming views, refer to Chapter 14, Inserting and Deleting Data.

Pervasive PSQL is case-sensitive when defining database element names. If you create a stored view named PhoNE, Pervasive PSQL stores the view name in the data dictionary as PhoNE. Pervasive PSQL is case-insensitive after you define the view name. After defining the stored view PhoNE, you can refer to it as phone.

Using stored views provides the following features:

•

You can store frequently executed queries and name them for later use. The following example creates a stored view named Phones based on the Department table.

CREATE VIEW Phones (PName, PPhone)
AS SELECT Name, Phone_Number
FROM Department#

•

You can specify the name of the stored view in table lists when retrieving, updating, and deleting data. The stored view behaves as though it is a table in the database, but it is actually reconstructed internally by the Pervasive PSQL engine each time it is used. The following example updates the phone number for the History Department in the Department table by referring to the stored view Phones.

UPDATE Phones
SET PPhone = '5125552426'
WHERE PName = 'History'#

•

You can specify headings. A heading specifies a column name that is different from the name you defined for the column in the dictionary. The following example specifies the headings Department and Telephone for the stored view Phones.

CREATE VIEW Dept_Phones (Department, Telephone)
AS SELECT Name, Phone_Number
FROM Department#

You can use the headings in subsequent queries on the view, as in the following example.

SELECT Telephone
FROM Dept_Phones#

If the selection list contains simple column names and you do not provide headings, Pervasive PSQL uses the column name as the column heading.

You must use headings to name constants and computed columns that you include in the view. The following example creates the headings Student and Total.

CREATE VIEW Accounts (Student, Total)
AS SELECT Student_ID, SUM (Amount_Paid)
FROM Billing
GROUP BY Student_ID#

You must also use headings if you specify SELECT * from multiple tables that have any duplicate column names.

•

You can create customized views for each user or application that accesses the database. You can store these view definitions within the data dictionary for later recall.

Read-Only Tables in Views

You cannot insert, update, or delete rows from views that contain read-only tables. (Here the term update refers to insert, update, and delete; if a table is read-only, you cannot update it.) Some tables are read-only whether or not they are in views that are specified as such; such tables are intrinsically read-only, and you cannot update them. A table is read-only if it meets one of the following criteria:

•

The database has security enabled, and the current user or user group has only SELECT rights defined for the database or the table.

•

The data files have been flagged read-only at the physical file level (for example, using the ATTRIB command under DOS or Windows or the chmod command in Linux).

•

You execute a SELECT clause that creates a view and contains any of the following items:

•

An aggregate function in the selection list.

•

A GROUP BY or HAVING clause.

•

A UNION.