CREATE VIEW

The CREATE VIEW statement defines a stored view or virtual table.

CREATE VIEW view-name [ ( column-name [ , column-name ]...) ]

[ WITH EXECUTE AS ‘MASTER’ ] AS query-specification

[ ORDER BY order-by-expression [ , order-by-expression ]... ]

view-name ::= user-defined-name

column-name ::= user-defined-name

order-by-expression ::= expression [ CASE (string) | COLLATE collation-name ] [ ASC | DESC ] (see SELECT syntax)

A view is a database object that stores a query and behaves like a table. A view contains a set of columns and rows. Data accessed through a view is stored in one or more tables; the tables are referenced by SELECT statements. Data returned by a view is produced dynamically every time the view is referenced.

See Table 1, Identifier Restrictions by Identifier Type, in Advanced Operations Guide for the maximum length of a view name. The maximum number of columns in a view is 256. View definitions have a 64 KB limit.

Pervasive PSQL supports grouped views. A grouped view is one that contains any of the following in the SELECT list:

•

•

•

•

Scalar Functions

•

Scalar subquery

•

•

Grouped views may be used in a subquery provided the subquery is an expression. A subquery connected with the operators IN, EXISTS, ALL, or ANY is not considered an expression.

View definitions cannot contain procedures.

The ORDER BY clause is functionally the same as the one described for the SELECT statement. See SELECT. Note the following, which are some of the key points:

•

Aliases may appear in the ORDER BY clause.

•

Scalar subqueries may appear in the ORDER BY clause.

•

ORDER BY clauses are not allowed in a subquery clause.

•

A view can contain a SELECT statement that includes both TOP and an ORDER BY clause. If so, the database engine generates a temporary table and populates it with the query’s entire result set. The rows in the temporary table are ordered as specified by the ORDER BY clause and the TOP number of rows in the ordered result set are returned.

•

Views that contain a TOP clause can be joined with other tables or views.

•

The use of a TOP clause is recommended in views that include an ORDER BY clause.

Trusted and Non-Trusted Views

A trusted view includes the WITH EXECUTE AS ‘MASTER’ clause. See Trusted and Non-trusted Objects.

The following statement creates a non-trusted view named vw_Person, which creates a phone list of all the people enrolled in a university. This view lists the last names, first names and telephone numbers with a heading for each column. The Person table is part of the DEMODATA sample database.

CREATE VIEW vw_Person (lastn,firstn,phone) AS SELECT Last_Name, First_Name,Phone FROM Person

In a subsequent query on the view, you may use the column headings in your SELECT statement:

SELECT lastn, firstn FROM vw_Person

The user executing the view must have SELECT permissions on the Person table.

============

The following example creates a similar view, but a trusted one.

CREATE VIEW vw_trusted_Person (lastn,firstn,phone) WITH EXECUTE AS ‘MASTER’ AS SELECT Last_Name, First_Name,Phone FROM Person

Now assume that you grant user “user1” SELECT permissions on vw_Person. User1 can use the column headings in a SELECT statement:

SELECT lastn, firstn FROM vw_trusted_Person

User1 is not required to have SELECT permissions on the Person table because the permissions were granted to the trusted view.

============

The following statement creates a view named vw_Person, which creates a phone list of all the people enrolled in a university. This view lists the last names, first names and telephone numbers with a heading for each column. The Person table is part of the DEMODATA sample database.

CREATE VIEW vw_Person (lastn, firstn, telphone) AS SELECT Last_Name, First_Name, Phone FROM Person

In a subsequent query on the view, you may use the column headings in your SELECT statement, as shown in the next example.

SELECT lastn, firstn FROM vw_Person

============

The example above can be changed to include an ORDER BY clause.

CREATE VIEW vw_Person_ordby (lastn, firstn, telphone) AS SELECT Last_Name, First_Name, Phone FROM Person ORDER BY phone

The view returns the following (for brevity, not all records are shown).

Last_Name First_Name Phone

========= ========== ==========

Vqyles Rex 2105551871

Qulizada Ahmad 2105552233

Ragadio Ernest 2105554654

Luckey Anthony 2105557628

Sokell Chester 2105559149

Gyduska Michael 5125550001

Happy Anthony 5125550004

Nix Anna 5125550006

============

The following example creates a view that returns the grade point average (GPA) of students in descending order, and, for each GPA ordering, lists the students by last name descending.

CREATE VIEW vw_gpa AS SELECT Last_Name,Left(First_Name,1) AS First_Initial,Cumulative_GPA AS GPA FROM Person LEFT OUTER JOIN Student ON Person.ID=Student.ID ORDER BY Cumulative_GPA DESC, Last_Name

The view returns the following (for brevity, not all records are shown).

Last_Name First_Initial GPA

========================= =============== ======

Ujazdowski T 4.000

Wotanowski H 4.000

Pabalan R 3.995

============

The following example creates a view that returns the top 10 records from the Person table, ordered by ID.

CREATE VIEW vw_top10 AS SELECT TOP 10 * FROM person ORDER BY id;

The view returns the following (for brevity, not all columns are shown).

ID First_Name Last_Name

========= ========== ==========

100062607 Janis Nipart

100285859 Lisa Tumbleson

100371731 Robert Mazza

100592056 Andrew Sugar

100647633 Robert Reagen

100822381 Roosevelt Bora

101042707 Avram Japadjief

101135758 Ismail Badad

101369010 Bruno Ippolite

101581226 Robert Obici

10 rows were affected.

============

The following example creates a view to demonstrate that ORDER BY can be used with UNION.

CREATE VIEW vw_union_ordby_desc AS SELECT first_name FROM person UNION SELECT last_name FROM PERSON ORDER BY first_name DESC

The view returns the following (for brevity, not all records are shown).

Trusted and Non-trusted Objects