 | SQL Engine Reference (v10) |  |
|
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-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:
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:
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.
In a subsequent query on the view, you may use the column headings in your SELECT statement:
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:
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.
In a subsequent query on the view, you may use the column headings in your SELECT statement, as shown in the next example.
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 ========================= =============== ====== Abuali I 4.000 Adachi K 4.000 Badia S 4.000 Rowan A 4.000 Ujazdowski T 4.000 Wotanowski H 4.000 Gnat M 3.998 Titus A 3.998 Mugaas M 3.995 Pabalan R 3.995
The following example creates a view that returns the top 10 records from the Person table, ordered 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).
First_Name =========== Zyrowski Zynda Zydanowicz Yzaguirre Yyounce Xystros Xyois Xu Wyont Wynalda Wykes
Trusted and Non-trusted Objects
|
Chapter contents
Prev topic: CREATE USER
|