Data Types - PostgreSQL: Up and Running - page 105

or an equivalent:
SELECT circuit_id , ( ac_volt ). r , ( ac_volt ). i FROM circuits ;
Puzzled by the parentheses surrounding ac_volt ? If you leave them
out, PostgreSQL will raise the error missing FROM-clause entry
for table “ac_volt” , because it assumes ac_volt without paren‐
theses refers to a table.
Building Operators and Functions for Custom Types
After you build a custom type such as a complex number, naturally you'll want to create
functions and operators for it. We'll demonstrate building a + operator for the com
plex_number we created. For more details about building functions, see Chapter 8 . As
stated earlier, an operator is a symbol alias for a function that takes one or two arguments.
You can find more details about what symbols and set of symbols are allowed in CREATE
OPERATOR .
In addition to being an alias, an operator contains optimization information that can
be used by the query optimizer to decide how indexes should be used, how best to
navigate the data, and which operator expressions are equivalent. More details about
these optimizations and how each can help the optimizer are in Operator Optimization .
The first step to creating an operator is to create a function, as shown in Example 5-34 .
Example 5-34. Add function for complex number
CREATE OR REPLACE FUNCTION add ( complex_number , complex_number ) RETURNS complex_num
ber AS
$$
SELECT ( ( COALESCE (( $ 1 ). r , 0 ) + COALESCE (( $ 2 ). r , 0 )),
( COALESCE (( $ 1 ). i , 0 ) + COALESCE (( $ 2 ). i , 0 )) ):: complex_number ;
$$
language sql ;
The next step is to create a symbolic operator to wrap the function, as in Example 5-35 .
Example 5-35. + operator for complex number
CREATE OPERATOR + (
PROCEDURE = add ,
LEFTARG = complex_number ,
RIGHTARG = complex_number ,
COMMUTATOR = + );
We can then test our new + operator:
SELECT ( 1 , 2 ):: complex_number + ( 3 , - 10 ):: complex_number ;
which outputs (4,-8) .