Chapter 3. Administrator Reference
This section describes how to administer your clusters using Administrator. The examples given use the following design, which is a database named web_store that has a user-defined schema named web_store_schema containing four tables.
 | Note |
|---|---|
The structure below is used only for table-related operations. Other examples use the public schema. |
To learn more about any of these commands, refer to the PostgreSQL 7.3 Reference Manual.
All fields (other than Name fields) that require you to specify more than one object name at once require you to quote the objects individually according to the SQL conventions. Examples of such fields include the Argument List and the Function Definition fields in CREATE FUNCTION, the SQL SELECT Statement field in CREATE VIEW, the Column Names and the SQL SELECT Statement fields in CREATE TABLE AS, and so on.
3.1. Aggregates
In the object hierarchy under every schema are Aggregates. You can add an aggregate by selecting CREATE AGGREGATE from the Aggregate context menu. Once an aggregate has been created, it can be commented on or dropped. This section describes how to create, comment on, and drop an aggregate with Administrator.
In this example, you will create an aggregate called total_cost.
Before creating an aggregate, you must create the state transition function to be used in defining the aggregate. Expand the database under which you wish to create the function (click the [O-] beside the database name) and then expand the public schema.
Right-click on Functions and select CREATE FUNCTION. The CREATE FUNCTION dialog appears.
You will create a function called "cost". It will take as input the cost and quantity of an item (both specified as numeric) and return the cost of that quantity of the item.
In the CREATE FUNCTION dialog, for the name for the new function type: cost
In the Arguments List, type:
numeric(7,2), numeric(7,2)
From the Language drop-down list, select sql.
As the Return Type, type: numeric(7,2)
For the Function Definition, type:
SELECT $1 * $2 AS RESULT;
Click OK to define this function.
An aggregate is an object under a schema, so to create a user-defined aggregate, first expand the database under which you wish to create the aggregate (click the [O-] beside the database name) and then expand the public schema.
Right-click on Aggregates and select CREATE AGGREGATE. The CREATE AGGREGATE dialog appears.
The first field is the name for the aggregate. Type the name: total_cost
This aggregate will be used to determine the total cost of all items in inventory.
For the Base Type, select numeric; for the State Transition Function, select the public schema and the function cost(pg_catalog.numeric,pg_catalog.numeric); and for the State Type select numeric.
Click OK to define this aggregate.
Note that the aggregate total_cost(numeric) has been added under the Aggregates node in the Tree View.
Administrator also enables you to perform the following operations on existing aggregates:
- Dropping
To drop an aggregate, right-click on it and select either DROP RESTRICT or DROP CASCADE. DROP RESTRICT will not drop the aggregate if there are any dependent objects. DROP CASCADE automatically drops the aggregate and all objects that depend on it.
If Preferences => Ask For Confirmations is enabled, you are asked to confirm that you want the aggregate dropped (and all the dependent objects in case of a DROP CASCADE); if it is disabled, the aggregate is dropped immediately (along with its dependent objects if DROP CASCADE was selected). This action cannot be undone.
- Commenting
PostgreSQL allows commenting on aggregates, which makes them easier to identify. To comment on an aggregate, right-click on it and select COMMENT. A dialog asks for the comment. If there is already a comment on the aggregate, this comment is displayed. Type the new comment in the edit box and click OK to save the new comment.
To learn more about aggregates, refer to the PostgreSQL 7.3 Reference Manual.