3.17. Operators

In the object hierarchy of every schema are Operators. You can add operators by selecting CREATE OPERATOR from the Operators context menu. Once an operator has been created, it can be commented on and dropped. This section describes how to create, comment on, and drop an operator with Administrator.

For more information on operators, refer to Operators under the Extending SQL chapter of the PostgreSQL 7.3 Administrator's Guide.

When creating an operator, you must set the Name and Procedure parameters.

The Name is the name of the new operator. The Procedure is the function used to implement this operator. This corresponds to the func_name parameter of the CREATE OPERATOR command, and only functions already defined in the database may be chosen.

Note

Whether an operator is unary or binary is determined by the number of arguments to the function: If the function takes two arguments, then the operator must be binary, and the Left Hand Argument and Right Hand Argument entered in the dialog must correspond to the function's arguments (in order). If the function takes only one argument, then the operator must be unary, but there is a choice as to whether the operator is left unary or right unary. To make an operator left unary, enter the type of the function's sole argument as the Left Hand Argument and leave the entry box for the Right Hand Argument empty. To make an operator right unary, do the reverse.

The remainder of the CREATE OPERATOR parameters are optional. For information on these parameters, see CREATE OPERATOR in the PostgreSQL 7.3 Reference Manual.

This example describes how to define a new operator called @$.

1. An operator class is an object under a schema, so to create an operator, first expand the database under which you wish to create the operator (click the [O-] beside the database name) and then expand the public schema.

2. Right-click on Operators and select CREATE OPERATOR. The CREATE OPERATOR dialog appears.

   You will create an operator named @$, which will be a left unary operator that does exactly what the function get_amount_by_customer does. If you have dropped that function, please re-create it now.

3. The first field is for the name of the operator. Type: @$

4. From the Procedure drop-down list, select: get_amount_by_customer(pg_catalog.int4)

5. As the Left Hand Argument, select: int4

   As this is a left unary operator, you do not need to enter anything for the Right Hand Argument. (Had this been a binary operator, a Right Hand Argument would have been required.) You may also leave all of the optional parameters for this operator blank.

   

   Figure 3-55. The CREATE OPERATOR Dialog

6. Click OK to define the new operator.

Note that the operator @$(LEFT = int4)->numeric has been added under the Operators node in the Tree View.

Administrator also enables you to perform the following operations on existing operators: