In the object hierarchy under every schema are Views. You can add views by selecting CREATE VIEW from the Views context menu. Once a view has been created, it can be commented on, renamed, replaced, and dropped. This section describes how to create, manage, and drop a view with Administrator.
PostgreSQL allows you to define views. A view can be thought of as a table (in most aspects, it is a table). 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 schema, so to create a view, first expand the database you wish to create the view under (click the [O-] beside the database name) and then expand the public schema.
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 web_store_schema.customers, web_store_schema.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.
Administrator enables you to save (export) the query in the SQL SELECT Statement box and to 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.
Administrator also enables you to perform the following operations on existing views:
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.
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 to save it.
To drop a view, right-click on it and select either DROP RESTRICT or DROP CASCADE. DROP RESTRICT will not drop the view if there are any dependant objects. DROP CASCADE automatically drops the view and all objects that depend on it.
If Preferences => 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.
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.
PostgreSQL 7.3 allows you to replace the definition of a view. To do so, right click on the view and select REPLACE VIEW. The dialog, which is similar to the CREATE VIEW dialog, can be edited as needed. Click OK to make the changes permanent.
To learn more about views, refer to the PostgreSQL 7.3 Reference Manual.