Views
In the object hierarchy under any database, you will find Views. Views can be added by selecting CREATE VIEW from the Views context menu. Once a view has been created, it can also be commented on, renamed, and dropped. This section describes how to create, manage, and drop a view with RHDB Administrator.
PostgreSQL allows you to define views on a database. A view can be thought of as a table (in most aspects, it is a table except that it cannot be updated in the current backend version). As the name suggests, a view provides a view of specific areas of the database. You can select specific columns from specific tables to create a view. Then, each time you want to check those columns, simply look up the view.
In this example, you will create a view named customer_invoices_view.
A view is an object under a database, so to create a view, first expand the database you wish to create the view under (click the + beside the database name).
Right-click on Views and select CREATE VIEW. The CREATE VIEW dialog appears.
The first field is the name for the view. As the database treats tables and views similarly, a view name cannot be the same as a table name in the same database. Type the name: customer_invoices_view
The next text-entry area is the SQL SELECT Statement box. Type the following query in the edit box:
SELECT customers.name, invoices.invoice_number, invoices.invoice_stamp FROM customers, invoices WHERE customer.customer_id=invoices.customer_id;
The above query displays the customer name followed by the invoice numbers and invoice timestamps of their invoices.
Once the query data is set, click OK to create the view.
Note that the view customer_invoices_view has been added under the Views node in the Tree View.
 | RHDB Administrator lets you save (export) the query in the SQL SELECT Statement box and import a previously saved query. To export a query, click Export To File and name the file in which the store the query. To import a query, click Import From File and select the file in which the query resides. |
The view created can now be called directly as SELECT * FROM customer_invoices_view instead of typing the query each time. Having this view also makes it easy to specify rules that apply to both customers and invoices tables at once.
RHDB Administrator also enables you to perform the following operations on existing views:
- Dropping
To drop a view, right-click on it and select DROP VIEW. If View -> Ask For Confirmations is enabled, you are asked to confirm that you want the view dropped; if it is disabled, the view is dropped immediately. This action cannot be undone.
- Renaming
A view can be renamed once it has been created. To rename a view, right click on it and select RENAME VIEW. A dialog asks for the new name. Type the new name and click OK.
Renaming views is not supported in backends below 7.2. Doing so on such backends will cause the view definition query to be lost!
- Altering Owner
By default, the user creating the view is the owner. However, the owner can later be modified by a superuser. To modify the owner of a view, right-click on the view and select ALTER VIEW OWNER. A dialog asks for the new owner name. Because the owner name can be only one of the users in the cluster, the dialog has a drop down box from which the user can be selected. Once the username is selected, click OK to change the view owner.
- Commenting
PostgreSQL allows commenting on views, which makes them easier to identify. To comment on a view, right-click on it and select COMMENT. A dialog asks for the comment. If there is already a comment on the view, this comment is displayed. Type the new comment in the edit box and click OK (or press
[Enter] ) to save the new comment.
Refer to the Red Hat Database SQL Guide and Reference for more information on views.