Extending Operators

I l @ ve RuBoard

PostgreSQL uses operators as the method by which data comparisons or aggregations are done. There are three general classes of PostgreSQL operators: left unary, right unary, and binary.

Binary operators are perhaps the most common. In essence, an operator is binary when it will sit between two separate data types (for example, 21 > 20). A classic example of a binary data type is the greater-than symbol (>); it sits between two data elements and returns a Boolean value from the evaluation of each element. Even more basic is the addition operator (+), which sums the values on each side and returns a result (for example, 2 + 3 returns 5).

Unary operators only accept data from one side, hence the names left unary or right unary. An example of a right-unary operator is the factorial operator (!); it sits on the left of an integer and provides the factorial result (for example, !4).

Operators must be defined for the specific data types they are required to act on. For instance, the > operator performs different actions depending on whether integers or geometric elements are being evaluated. Because of that, it is necessary to explicitly type the specific data types that custom operators are designated to operate on.

Defining a Custom Operator

Before an operator can be defined, the underlying function must first be created. These functions either can be defined as procedural functions (for example, SQL, PL/pgSQL, and so on) or can link to a compiled C object file.

In this example, a function is created that accepts two integers. It adds these integers. If the result is greater than 100, a TRUE value is returned; otherwise , it returns FALSE . A simple SQL function is created to perform this action, as follows :

 CREATE FUNCTION addhund (int4, int4) RETURNS boolean AS '  BEGIN          IF ( + ) > 100 THEN              RETURN 't';          END IF;          RETURN 'f';  END;  ' LANGUAGE 'plpgsql' WITH (( iscachable ); 

You can then test this function directly:

 SELECT addhund(99,99) AS answer;  answer  ------ t  SELECT addhund(9,9) AS answer;  answer  ------ f 

Next, this function is bound to a specific operator character through the use of the CREATE OPERATOR command:

 CREATE OPERATOR +++ (      leftarg=int4,       rightarg=int4,       procedure=addhund,       commutator= +++); 

The preceding command specifies that it is a binary operator that expects int4 data types on both the left and right sides. Additionally, it specifies that the COMMUTATOR optimization for this operator is itself.

This new operator can be tested as follows:

 SELECT 11 +++ 90 AS answer;  answer  ------ t  SELECT 9 +++ 90 AS answer;  answer  ------ f 

Optimization Notes

Operator optimization pertains to giving the database clues as to how the various operators relate to each other. There are several optimization settings that can be specified upon operator creation.

COMMUTATOR

In a previous example, you defined the COMMUTATOR optimization for your + + + operator to be itself. Generally , COMMUTATOR specification only makes sense for binary operators. It describes what relations should exist if the data on each side of the operator were switched. For instance, consider the following relations between the standard addition operator:

3 + 8 = 11

8 + 3 = 11

You can see that the addition operator is commutative with itself. This means that it doesn't matter what side each individual data element is on; the results will be the same. In contrast, this differs with regard to how the subtraction operator works:

3 “ 8 = “5

8 “ 3 = 5

In this case, the position of the data elements does make a difference. Therefore, subtraction is not commutative with itself.

NEGATOR

Another phrase that can be specified during operator creation is what, if anything, negates the current definition. For instance, the equal operator is negated by the unequal operator (for example, a = b is negated by a <> b).

RESTRICT

The RESTRICT optimization clause is only valid for binary operators that return a Boolean result (for example, a > b). Restriction provides hints to the query optimizer related to the particular selectivity that would satisfy a general WHERE clause. The standard estimators are as follows:

Estimator

Description

Used For

eqsel

Equal to selection

=

neqsel

Not equal to selection

<>

scalarltsel

Scalar less-than selection

< or <=

scalargtsel

Scalar greater-than selection

> or >=

JOIN

JOIN optimization is generally only valid for binary operators that return Boolean results (for example, a = b). The JOIN optimizer provides insight as to how many rows would match between a pair of tables selected with a general WHERE clause (for example, payroll.empid=employee.empid ).

The possible values that can be specified for an estimation clause are shown in Table 14.3.

Table 14.3. Values for an Estimation Clause

Estimator

Description

Used For

eqjoinsel

Equal to selection

=

nejoinqsel

Not equal to selection

<>

scalarltjoinsel

Scalar less-than selection

< or <=

scalargtjoinsel

Scalar greater-than selection

> or >=

areajoinsel

2D area comparisons

N/A

positionjoinsel

2D position comparisons

N/A

contjoinsel

2D containment comparisons

N/A

HASHES

If the HASHES clause is present, the optimizer is instructed that it is permissible to attempt hash joins on this operator. HASHES are only valid for binary operators that return Boolean results.

In general, this only makes sense when the operator represents absolute equality between the data types (for example, a = b). If the operator does not provide an equality comparison between the operators, hash joins would be of little use.

SORT1 and SORT2

This set of clauses instructs the optimizer whether it is permissible to attempt merge joins on either the left or right side of the operator.

Use of these optimization options is very limited. In practice, it is usually only valid for the equal (=) operator. Moreover, the two referenced operators should always be named < .

The CREATE OPERATOR command does not perform any sanity checks to determine the validity of optimization options. Therefore, the command might successfully create the specified operator, but it might still fail on use. In fact, using the SORT1/SORT2 optimization options will cause failure if either of the following conditions is not met:

  • The merge join equality operator must have a commutator (should be itself if the two data types are the same).

  • There must be < and > operators that have the same data types as the specified sort operator.

I l @ ve RuBoard


PostgreSQL Essential Reference
PostgreSQL Essential Reference
ISBN: 0735711216
EAN: 2147483647
Year: 2001
Pages: 118
Authors: Barry Stinson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net