Adding Operators
| Adding the datatype is not enough. New datatypes don't support functions yet, because only the in and out functions are defined so far. We have already defined the datatype itself, but as you can see in the following, our datatype is not very useful yet: mydata=# SELECT * FROM mycolor WHERE cfield='(0, 20, 0)'::color; ERROR: Unable to identify an operator '=' for types 'color' and 'color' You will have to retype this query using an explicit cast To make the datatype more powerful, we have to implement additional functions and assign them to operators. One of the most important operators for a datatype is the + operator. Without it, there is no way to perform additions. In the following listing, you can see how a prototype of a + operator can be defined for our datatype: /* define + operator */ color *color_add(color *a, color *b) { color *c; /* checking for NULL */ if ((a == NULL) (b == NULL)) { return(NULL); } /* adding the two objects */ c->r = a->r + b->r; c->g = a->g + b->g; c->b = a->b + b->b; /* if a component is > 255 -> set it to 255 */ c->r = (255 < c->r) ? 255 : c->r; c->g = (255 < c->g) ? 255 : c->g; c->b = (255 < c->b) ? 255 : c->b; return c; } This code is still very simple. We just add the various elements of the datatype and make sure that none of the values is higher than 255 . Finally, the result is returned. Before we can use the operator in combination with the datatype, we have to add the function and the operator to the database. Some things have to be defined: CREATE FUNCTION color_add(color, color) RETURNS color AS '/usr/local/postgres_extpack/libfoo.so' LANGUAGE 'c'; CREATE OPERATOR + ( leftarg = color, rightarg = color, procedure = color_add, commutator = + ); We add the function the same way that we have before. Defining the operator is also an easy task. The first argument passed to the CREATE OPERATOR function is the datatype of the left argument; the second argument defines the right argument of the + operator. Because we want to add two colors, the right and the left argument have to be color . If we want to add color and an integer, for instance, we have to change the function assigned to the operator or add a second function for handling int4 + color (instead of color + color ). The third object tells PostgreSQL which function has to be used for processing an addition of two colors. The last parameter is a little more complicated. In case of an addition, it does not matter that we want to compute the result of color1 + color2 or color2 + color1 ”the result will be the same. Therefore, the commutator of + is + . In other words, the commutator is the operator that has to be used if the statement is written the other way around ( color2 + color1 instead of color1 + color2 ). In case of < and > , the situation is different: a < b is not equal to b < a . If we want to write b before a , the result is b > a . > is therefore the commutator of the < operator. Many operators have a commutator, but not all of them. The - operator, for instance, is not commutative. The definition of a commutator is additional information for optimizing the datatype. Before we get to additional optimization information, let's look at how the + operator can be used for our datatype. here is a simple example: mydata=# SELECT '(0, 20, 255)'::color + '(0, 20, 255)'::color; ?column? -------------- (0, 40, 255) (1 row) The two values are added, and our function makes sure that none of the values is higher than 255 . If we want to add NULL to a value, NULL is returned: mydata=# SELECT '(0, 20, 255)'::color + NULL::color; ?column? ---------- (1 row) Another important operator is the = operator, which we use to find out whether two values are equal. Without a = operator defined for our datatype, it is not possible to write useful WHERE clauses, because we have to compare values. In the following code, you can see how the = operator has been implemented for our datatype; it checks whether all three components of the datatype are the same. If they are, true is returned; otherwise , the result of the function is false : /* function for "=" operator */ bool *color_abs_eq(color *a, color *b) { bool *retvalue; /* checking whether the values are equal */ if ((a->r == b->r) && (a->g == b->g) && (a->b == b->b)) { retvalue = 1; } else { retvalue = 0; } return retvalue; } To add the operator to the database, we add the function in the shared object, which we have compiled using the Makefile we have shown before. After adding the function, we add the operator and define the left and the right operator, as we did in the previous example: CREATE FUNCTION color_abs_eq(color, color) RETURNS bool AS '/usr/local/postgres_extpack/libfoo.so' LANGUAGE 'c'; CREATE OPERATOR = ( leftarg = color, rightarg = color, procedure = color_abs_eq, restrict = eqsel, join = eqjoinsel ); We have also included a restrict clause in the command. restrict helps the optimizer find the right way through a WHERE clause. Four standard restriction estimators are defined:
Note Defining restriction estimators might at first sight seem useless, but it helps the optimizer to find the fastest way through a query. Imagine a query that selects data from a table. With the help of restriction estimators the optimizer tries to guess the amount of rows found or rejected. The information used by the optimizer can be generated by using VACUUM ANALYZE. Let's have a look at our new operator in action. We insert two records into the database and query the table using a SELECT statement including a WHERE clause: mydata=# INSERT INTO mycolor VALUES ('(53, 66, 0)'::color); INSERT 120166 1 mydata=# INSERT INTO mycolor VALUES ('(-10, 20, 300)'::color); INSERT 120167 1 mydata=# SELECT * FROM mycolor; cfield -------------- (53, 66, 0) (0, 20, 255) (2 rows) mydata=# SELECT * FROM mycolor WHERE cfield='(0, 20, 255)'::color; cfield -------------- (0, 20, 255) (1 row) One of the two records that we have inserted into the database matches the WHERE clause, and therefore it is displayed. If something is wrong with the underlying C function, it is very likely that the backend crashes. Keep this in mind when dealing with datatypes and operators. After we define the = operator, we want to define the <> operator. Here is the required code: /* function for "<> operator */ bool *color_abs_ne(color *a, color *b) { bool *retvalue; /* checking whether the values are equal */ if ((a->r == b->r) && (a->g == b->g) && (a->b == b->b)) { retvalue = 0; } else { retvalue = 1; } return retvalue; } You can see that the C function used for the <> operator is very similar to the function used for the = operator. Now let's have a look at the SQL code that we need to insert the new operator into the database. This time we included a more sophisticated version of the SQL code: CREATE FUNCTION color_abs_ne(color, color) RETURNS bool AS '/usr/local/postgres_extpack/libfoo.so' LANGUAGE 'c'; CREATE OPERATOR <> ( leftarg = color, rightarg = color, procedure = color_abs_ne, restrict = neqsel, join = neqjoinsel, commutator = <>, negator = = ); The function is inserted into the database as we did before, but let's take a closer look at the definition of the operator. The restrict clause contains neqsel this time (according to the four standard restriction estimators we listed previously). The commutator of the operator is <> , because it makes no difference whether we want to compute the result of a <> b or b <> a . Another important parameter that can be passed to the function is the negator. The negator defined the operator, which returns the same result, when the two operands of the statement are swapped. In other words, = is the negator of <> , because NOT (a <> b) is equal to a = b . Another component of the CREATE OPERATOR command shown previously defines the behavior of the database when performing joins. If a join clause is provided, the join selectivity estimation function for the operator is defined. In general, join clauses make sense only for binary operators that return Boolean. The idea behind a join clause is to provide a function that estimates the fraction of rows satisfying a WHERE clause. In other words, It helps the optimizer to guess how many rows will most likely have to be omitted and how many rows can be taken from a table. Several types of "join selectivity estimation function" are provided by PostgreSQL:
Not only join clauses can be used. We have dealt with other parameters before, but PostgreSQL provides even more. A HASHES clause can be used to tell the system that it is okay to use the hash join method for a join based on the operator. If we want to define SORT clauses tell the system, that it is allowed to use merge joins. Now that we have defined the operator, let's look at how it can be used in SQL: mydata=# INSERT INTO mycolor VALUES ('(-10, 20, 300)'::color); INSERT 120229 1 mydata=# SELECT * FROM mycolor; cfield -------------- (0, 20, 255) (1 row) mydata=# SELECT * FROM mycolor WHERE cfield <> '(0, 20, 255)'::color; cfield -------- (0 rows) There is absolutely no difference between using our <> operator and PostgreSQL's built-in <> operator.  |
