Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page

Creating New Operators

PostgreSQL allows the creation of custom operators in addition to custom functions. Operators are sometimes called syntactic sugar for functions. This is because, technically, an operator is just an alternate syntax for an existing function. For example, the addition operator (+) actually calls one of several built-in functions, including numeric_add(). For example:

SELECT 1 + 2 AS by_operator, numeric_add(1,2) AS by_function;

 by_operator | by_function
           3 |           3
(1 row)

An operator definition defines what data types it operates on, and which side of the operator to expect a value of the given data type to be found on (left, right, or both). It also defines the function that is called, passing the values that are being operated on as arguments to that function.

Creating an operator

The CREATE OPERATOR SQL command creates a new operator. Here is the syntax for CREATE OPERATOR:


                  [, LEFTARG = 
                  [, RIGHTARG = 
                  [, COMMUTATOR = 
                  [, NEGATOR = 
                  [, RESTRICT = 
                  [, JOIN = 
                  [, HASHES ]
                  [, SORT1 = 
                  [, SORT2 = 
 ] )

In this syntax, name is the name of the new operator, and functionname is the name of the function to be called by the operator. The remaining clauses are all optional, though at least one of the LEFTARG or RIGHTARG clauses must be applied. Note that the operator name may only consist of the following accepted characters:

 + - * / < > = ~ ! @ # % ^ & | ` ? $

Note: See the reference entry on CREATE OPERATOR for more information on the remaining optional clauses, and further restrictions on the operator name.

Specifying only the LEFTARG data type creates an operator that operates only on a value (e.g., a constant or identifier) to its left. Conversely, specifying only the RIGHTARG data type creates an operator that operates only on a value to its right. Specifying both a LEFTARG and RIGHTARG type results in an operator that operates on a value to both the left and right.

The factorial operator (!) is an example of a built-in operator that affects values to its left, while the addition operator (+) is a good example of an operator that affects values both on the left and right of the operator. Note that the functionname must accept the appropriate number of arguments as implied by the use of the LEFTARG and RIGHTARG keywords (either one or two arguments). Furthermore, the function's accepted argument types should match the relevant operator types defined by CREATE OPERATOR for each respective value to be operated on.

Example 7-53 creates an operator named !#, which passes the value to its left to the is_zero() function (defined in Example 7-49). This means that the syntax of value !# will be effectively identical to using the functional syntax of is_zero(value).

Example 7-53. Creating a user-defined operator


                   LEFTARG = integer);


The CREATE message returned by Example 7-53 indicates that the operator was successfully created. As with functions, any user connected to the database will be able to use the new operator. The operator is owned by the user who creates it, meaning that no other user may remove it (unless they have superuser rights). Example 7-54 demonstrates the use of the new !# operator to check for books that are out of stock in Book Town's stock table.

Example 7-54. Using a user-defined operator

SELECT * FROM stock WHERE stock !#;

    isbn    | cost  | retail | stock
 0394900014 | 23.00 |  23.95 |     0
 0451198492 | 36.00 |  46.95 |     0
 0451457994 | 17.00 |  22.95 |     0
(3 rows)

Overloading an operator

Operators may become overloaded in much the same way as functions. This means that an operator is created with the same name as an existing operator, but affects a different set of defined types. More than one operator may have the same name, although two operators may not share the same name if they accept the same argument definitions. As long as a function exists to accept the number and type of arguments implied by the type of operator defined, though, the operator may be overloaded.

Example 7-53 overloads the !# operator. The first CREATE OPERATOR statement creates a similar operator to the one created in Example 7-53. However, it specifies a RIGHTARG clause rather than a LEFTARG clause, resulting in a version of the operator with the same name which operates on an argument of type integer to the right of the operator, rather than the left. The second statement creates a third variant of the !# operator, which operates on both an argument to the left and right of the operator, simultaneously.

Example 7-55. Overloading a user-defined operator


                    RIGHTARG = integer);


                    LEFTARG = integer,

                    RIGHTARG = integer);


Example 7-55 overloads the !# operator with the same is_zero() function because the function itself was overloaded in Example 7-50 in the Section called Creating C functions ," earlier in this chapter. As there are two copies of the is_zero() function--one that accepts one argument, and one that accepts two--the !# operator can be safely overloaded to accept both a single argument (on either the left or right side), as well as to accept parameters from both sides.

Once overloaded, PostgreSQL's parser can correctly interpret each defined operator in your SQL statements. Example 7-56 demonstrates three uses of the same operator, with different left and right arguments. Each use is valid, as the operator was overloaded in Example 7-55.

Example 7-56. Using an overloaded operator

SELECT isbn, stock FROM stock

                   WHERE stock !#

                   AND !# stock

                   AND stock !# stock;

    isbn    | stock
 0394900014 |     0
 0451198492 |     0
 0451457994 |     0
(3 rows)

Dropping an operator

An operator can be dropped with the DROP OPERATOR command. An operator may only be dropped by the user who created it, or by a PostgreSQL superuser.


The DROP OPERATOR command applies to built-in operators as well as user-defined operators, so take care with this command if executing it as a superuser!

As operators are defined by their arguments as well as their name, DROP OPERATOR requires that you specify the left and right argument types of the operator. If there is no type for a given side, specify the NONE keyword.

Here is the syntax for DROP OPERATOR:

 ( { 
 | NONE } , { 
 | NONE } )

Example 7-57 drops the variant of the !# operator that affects both left and right arguments.

Example 7-57. Dropping an operator

DROP OPERATOR !# (integer, integer);


Example 7-58 uses very similar syntax to Example 7-57, but drops the overloaded operator that affects only arguments to the right of the operator.

Example 7-58. Dropping an overloaded operator

DROP OPERATOR !# (NONE, integer);

Databases - Practical PostgreSQL
Previous Page Home Next Page

  Published under the terms of the Open Publication License Design by Interspire