Defining the Data Type in PostgreSQL
At this point, PostgreSQL knows about your input and output functions. Now you can tell PostgreSQL about your data type:
CREATE TYPE FCUR ( INPUT=fcur_in, OUTPUT=fcur_out, INTERNALLENGTH=12 );
This command creates a new data type (how exciting) named FCUR. The input function is named fcur_in, and the output function is named fcur_out. The INTERNALLENGTH=12 clause tells PostgreSQL how much space is required to hold the internal value. I computed this value by hand?just add up the size of each member of the fcur structure and be sure that you account for any pad bytes. The safest way to compute the INTERNALLENGTH is to use your C compiler's sizeof() operator.
Let's create a table that uses this data type and insert a few values:
movies=# CREATE TABLE fcur_test( pkey INT, val FCUR ); CREATE movies=# INSERT INTO fcur_test VALUES( 1, '1' ); INSERT movies=# INSERT INTO fcur_test VALUES( 2, '1(.5)' ); INSERT movies=# INSERT INTO fcur_test VALUES( 3, '3(1/US$)' ); INSERT movies=# INSERT INTO fcur_test VALUES( 4, '5(.687853/GBP)' ); INSERT movies=# INSERT INTO fcur_test VALUES( 5, '10(7.2566/FRF)' ); INSERT movies=# INSERT INTO fcur_test VALUES( 6, '1(1.5702/CA$)' ); INSERT movies=# INSERT INTO fcur_test VALUES( 7, '1.5702(1.5702/CA$)' ); INSERT
Now let's see what those values look like when you retrieve them:
movies=# SELECT * FROM fcur_test;
pkey | val
------+--------------------
1 | 1(1/???)
2 | 1(0.5/???)
3 | 3(1/US$)
4 | 5(0.687853/GBP)
5 | 10(7.2566/FRF)
6 | 1(1.5702/CA$)
7 | 1.5702(1.5702/CA$)
Not bad. The question marks are kind of ugly, but the data that you put in came back out.
At this point, you officially have a new data type. You can put values in and you can get values out. Let's add a few functions that make the FCUR type a little more useful.
It would be nice to know if two FCUR values represent the same amount of money expressed in your local currency. In other words, you want a function, fcur_eq, which you can call like this:
movies=# SELECT fcur_eq( '1', '1.5702(1.5702/CA$)' ); fcur_eq --------- t (1 row) movies=# SELECT fcur_eq( '1', '3(1.5702/CA$)' ); fcur_eq --------- f (1 row)
The first call to fcur_eq tells you that 1.5702 Canadian dollars is equal to 1 U.S. dollar. The second call tells you that 3 Canadian dollars are not equal to 1 U.S. dollar.
To compare two FCUR values, you need to convert them into a common currency:
102 /*
103 ** Name: normalize()
104 **
105 ** Converts an fcur value into a normalized
106 ** double by applying the exchange rate.
107 */
108
109 static double normalize( fcur * src )
110 {
111 return( src->fcur_units / src->fcur_xrate );
112 }
The normalize() function converts a given FCUR value into our local currency. You can use normalize() to implement the fcur_eq() function:
115 /*
116 ** Name: fcur_eq()
117 **
118 ** Returns true if the two fcur values
119 ** are equal (after normalization), otherwise
120 ** returns false.
121 */
122
123 PG_FUNCTION_INFO_V1(fcur_eq);
124
125 Datum fcur_eq(PG_FUNCTION_ARGS)
126 {
127 fcur * left = (fcur *)PG_GETARG_POINTER(0);
128 fcur * right = (fcur *)PG_GETARG_POINTER(1);
129
130 PG_RETURN_BOOL( normalize( left ) == normalize( right ));
131 }
132
This function is straightforward. You normalize each argument, compare them using the C == operator, and return the result as a BOOL Datum. You declare this function as ISSTRICT so that you don't have to check for NULL arguments.
Now you can compile your code again and tell PostgreSQL about your new function (fcur_eq()):
$ make -f makefile fcur.so $ psql -q movies=# CREATE OR REPLACE FUNCTION fcur_eq( fcur, fcur ) movies-# RETURNS bool movies-# AS 'fcur.so' LANGUAGE 'C' movies-# WITH ( ISCACHABLE, ISSTRICT );
Now you can call this function to compare any two FCUR values:
movies=# SELECT fcur_eq( '1', '1.5702(1.5702/CA$)' ); fcur_eq --------- t (1 row) movies=# SELECT fcur_eq( '1', NULL ); fcur_eq --------- (1 row)
The fcur_eq function is nice, but you really want to compare FCUR values using the = operator. Fortunately, that's easy to do:
movies=# CREATE OPERATOR = movies-# ( movies-# leftarg = FCUR, movies-# rightarg = FCUR, movies-# procedure = fcur_eq, movies-# );
This command creates a new operator named =. This operator has a FCUR value on the left side and a FCUR value on the right side. PostgreSQL calls the fcur_eq function whenever it needs to evaluate this operator.
Now you can evaluate expressions such as
movies=# SELECT * FROM fcur_test WHERE val = '1';
pkey | val
------+--------------------
1 | 1(1/???)
7 | 1.5702(1.5702/CA$)
(2 rows)
The operator syntax is much easier to read than the functional syntax. Let's go ahead and add the other comparison operators: <>, <, <=, >, and >=. They all follow the same pattern as the = operator: You normalize both arguments and then compare them as double values.
133 /*
134 ** Name: fcur_ne()
135 **
136 ** Returns true if the two fcur values
137 ** are not equal (after normalization),
138 ** otherwise returns false.
139 */
140
141 PG_FUNCTION_INFO_V1(fcur_ne);
142
143 Datum fcur_ne(PG_FUNCTION_ARGS)
144 {
145 fcur * left = (fcur *)PG_GETARG_POINTER(0);
146 fcur * right = (fcur *)PG_GETARG_POINTER(1);
147
148 PG_RETURN_BOOL( normalize( left ) != normalize( right ));
149 }
150
151 /*
152 ** Name: fcur_lt()
153 **
154 ** Returns true if the left operand
155 ** is less than the right operand.
156 */
157
158 PG_FUNCTION_INFO_V1(fcur_lt);
159
160 Datum fcur_lt(PG_FUNCTION_ARGS)
161 {
162 fcur * left = (fcur *)PG_GETARG_POINTER(0);
163 fcur * right = (fcur *)PG_GETARG_POINTER(1);
164
165 PG_RETURN_BOOL( normalize( left ) < normalize( right ));
166 }
167
168 /*
169 ** Name: fcur_le()
170 **
171 ** Returns true if the left operand
172 ** is less than or equal to the right
173 ** operand.
174 */
175
176 PG_FUNCTION_INFO_V1(fcur_le);
177
178 Datum fcur_le(PG_FUNCTION_ARGS)
179 {
180 fcur * left = (fcur *)PG_GETARG_POINTER(0);
181 fcur * right = (fcur *)PG_GETARG_POINTER(1);
182
183 PG_RETURN_BOOL( normalize( left ) <= normalize( right ));
184 }
185
186 /*
187 ** Name: fcur_gt()
188 **
189 ** Returns true if the left operand
190 ** is greater than the right operand.
191 */
192
193 PG_FUNCTION_INFO_V1(fcur_gt);
194
195 Datum fcur_gt(PG_FUNCTION_ARGS)
196 {
197 fcur * left = (fcur *)PG_GETARG_POINTER(0);
198 fcur * right = (fcur *)PG_GETARG_POINTER(1);
199
200 PG_RETURN_BOOL( normalize( left ) > normalize( right ));
201 }
202
203 /*
204 ** Name: fcur_ge()
205 **
206 ** Returns true if the left operand
207 ** is greater than or equal to the right operand.
208 */
209
210 PG_FUNCTION_INFO_V1(fcur_ge);
211
212 Datum fcur_ge(PG_FUNCTION_ARGS)
213 {
214 fcur * left = (fcur *)PG_GETARG_POINTER(0);
215 fcur * right = (fcur *)PG_GETARG_POINTER(1);
216
217 PG_RETURN_BOOL( normalize( left ) >= normalize( right ));
218 }
Now you can tell PostgreSQL about these functions:
movies=# CREATE OR REPLACE FUNCTION fcur_ne( fcur, fcur ) movies-# RETURNS boolean movies-# AS 'fcur.so' LANGUAGE 'C' movies-# WITH( ISCACHABLE, ISSTRICT ); CREATE movies=# CREATE OR REPLACE FUNCTION fcur_lt( fcur, fcur ) movies-# RETURNS boolean movies-# AS 'fcur.so' LANGUAGE 'C' movies-# WITH( ISCACHABLE, ISSTRICT ); CREATE movies=# CREATE OR REPLACE FUNCTION fcur_le( fcur, fcur ) movies-# RETURNS boolean movies-# AS 'fcur.so' LANGUAGE 'C' movies-# WITH( ISCACHABLE, ISSTRICT ); CREATE movies=# CREATE OR REPLACE FUNCTION fcur_gt( fcur, fcur ) movies-# RETURNS boolean movies-# AS 'fcur.so' LANGUAGE 'C' movies-# WITH( ISCACHABLE, ISSTRICT ); CREATE movies=# CREATE OR REPLACE FUNCTION fcur_ge( fcur, fcur ) movies-# RETURNS boolean movies-# AS 'fcur.so' LANGUAGE 'C' movies-# WITH( ISCACHABLE, ISSTRICT ); CREATE
And you can turn each of these functions into an operator:
movies=# CREATE OPERATOR <> movies-# ( movies-# leftarg = fcur, movies-# rightarg = fcur, movies-# procedure = fcur_ne, movies-# commutator = <> movies-# ); CREATE movies=# CREATE OPERATOR < movies-# ( movies-# leftarg = fcur, movies-# rightarg = fcur, movies-# procedure = fcur_lt, movies-# commutator = > movies-#); CREATE movies=# CREATE OPERATOR <= movies-# ( movies-# leftarg = fcur, movies-# rightarg = fcur, movies-# procedure = fcur_le, movies-# commutator = >= movies-# ); CREATE movies=# CREATE OPERATOR > movies-# ( movies-# leftarg = fcur, movies-# rightarg = fcur, movies-# procedure = fcur_gt, movies-# commutator = < movies-# ); CREATE movies=# CREATE OPERATOR >= movies-# ( movies-# leftarg = fcur, movies-# rightarg = fcur, movies-# procedure = fcur_ge, movies-# commutator = <= movies-#); CREATE
Notice that there is a commutator for each of these operators. The commutator can help PostgreSQL optimize queries that involve the operator.
For example, let's say that you have an index that covers the balance column. With a commutator, the query
SELECT * FROM customers WHERE balance > 10 and new_balance > balance;
can be rewritten as
SELECT * FROM customers WHERE balance > 10 and balance < new_balance;
This allows PostgreSQL to perform a range scan using the balance index. The commutator for an operator is the operator that PostgreSQL can use to swap the order of the operands. For example, > is the commutator for < because if x > y, y < x. Likewise, < is the commutator for >. Some operators are commutators for themselves. For example, the = operator is a commutator for itself. If x = y is true, then y = x is also true.
There are other optimizer hints that you can associate with an operator. See the CREATE OPERATOR section of the PostgreSQL Reference Manual for more information.
I'll finish up this chapter by defining one more operator (addition) and two functions that extend the usefulness of the FCUR data type.
First, let's look at a function that adds two FCUR values:
259 /*
260 ** Name: fcur_add()
261 **
262 ** Adds two fcur values, returning the result
263 ** If the operands are expressed in the same
264 ** currency (and exchange rate), the result
265 ** will be expressed in that currency,
266 ** otherwise, the result will be in normalized
267 ** form.
268 */
269
270 PG_FUNCTION_INFO_V1(fcur_add);
271
272 Datum fcur_add(PG_FUNCTION_ARGS)
273 {
274 fcur * left = (fcur *)PG_GETARG_POINTER(0);
275 fcur * right = (fcur *)PG_GETARG_POINTER(1);
276 fcur * result;
277
278 result = (fcur *)palloc( sizeof( fcur ));
279
280 if( left->fcur_xrate == right->fcur_xrate )
281 {
282 if( strcmp( left->fcur_name, right->fcur_name ) == 0 )
283 {
284 /*
285 ** The two operands have a common currency - preserve
286 ** that currency by constructing a new fcur with the
287 ** same currency type.
288 */
289 result->fcur_xrate = left->fcur_xrate;
290 result->fcur_units = left->fcur_units + right->fcur_units;
291 strcpy( result->fcur_name, left->fcur_name );
292
293 PG_RETURN_POINTER( result );
294 }
295 }
296
297 result->fcur_xrate = 1.0;
298 result->fcur_units = normalize( left ) + normalize( right );
299 strcpy( result->fcur_name, baseCurrencyName );
300
301 PG_RETURN_POINTER( result );
302
303 }
This function returns a FCUR datum; at line 278, we use palloc() to allocate the return value. fcur_add() has a nice feature: If the two operands have a common currency and a common exchange rate, the result is expressed in that currency. If the operands are not expressed in a common currency, the result will be a value in local currency.
Lines 289 through 291 construct the result in a case where the operand currencies are compatible. If the currencies are not compatible, construct the result at lines 297 through 299.
Let's tell PostgreSQL about this function and make an operator (+) out of it:
movies=# CREATE OR REPLACE FUNCTION fcur_add( fcur, fcur ) movies-# RETURNS fcur movies-# AS 'fcur.so' LANGUAGE 'C' movies-# WITH( ISCACHABLE, ISSTRICT ); CREATE movies-# CREATE OPERATOR + movies-# ( movies-# leftarg = fcur, movies-# rightarg = fcur, movies-# procedure = fcur_add, movies-# commutator = + movies-# ); CREATE
Now, try it:
movies=# SELECT *, val + '2(1.5702/CA$)' AS result FROM fcur_test;
pkey | val | result
------+--------------------+--------------------
1 | 1(1/???) | 2.27372(1/US$)
2 | 1(0.5/???) | 3.27372(1/US$)
3 | 3(1/US$) | 4.27372(1/US$)
4 | 5(0.687853/GBP) | 8.54272(1/US$)
5 | 10(7.2566/FRF) | 2.65178(1/US$)
6 | 1(1.5702/CA$) | 3(1.5702/CA$)
7 | 1.5702(1.5702/CA$) | 3.5702(1.5702/CA$)
(7 rows)
Notice that the result values for rows 6 and 7 are expressed in Canadian dollars.
Creating other arithmetic operators for the FCUR type is simple. If the operands share a common currency (and exchange rate), the result should be expressed in that currency. I'll let you add the rest of the arithmetic operators.
The last two functions that I wanted to show you will convert FCUR values to and from REAL values. Internally, the REAL data type is known as a float4.
220 /*
221 ** Name: fcur_to_float4()
222 **
223 ** Converts the given fcur value into a
224 ** normalized float4.
225 */
226
227 PG_FUNCTION_INFO_V1(fcur_to_float4);
228
229 Datum fcur_to_float4(PG_FUNCTION_ARGS)
230 {
231 fcur * src = (fcur *)PG_GETARG_POINTER(0);
232
233 PG_RETURN_FLOAT4( normalize( src ));
234
235 }
The fcur_to_float4() function converts an FCUR value into a normalized FLOAT4 (that is, REAL) value. There isn't anything fancy in this function; let normalize() do the heavy lifting.
237 /*
238 ** Name: float4_to_fcur()
239 **
240 ** Converts the given float4 value into an
241 ** fcur value
242 */
243
244 PG_FUNCTION_INFO_V1(float4_to_fcur);
245
246 Datum float4_to_fcur(PG_FUNCTION_ARGS)
247 {
248 float4 src = PG_GETARG_FLOAT4(0);
249 fcur * result = (fcur *)palloc( sizeof( fcur ));
250
251 result->fcur_units = src;
252 result->fcur_xrate = 1.0;
253
254 strcpy( result->fcur_name, baseCurrencyName );
255
256 PG_RETURN_POINTER( result );
257 }
The float4_to_fcur() function is a bit longer, but it's not complex. You allocate space for the result using palloc(); then create the result as a value expressed in your local currency.
When you tell PostgreSQL about these functions, you won't follow the same form that you have used in earlier examples:
movies=# CREATE OR REPLACE FUNCTION FCUR( FLOAT4 ) movies-# RETURNS FCUR movies-# AS 'fcur.so','float4_to_fcur' movies-# LANGUAGE 'C' movies-# WITH( ISCACHABLE, ISSTRICT ); CREATE
Notice that the internal (C) name for this function is float4_to_fcur(), but the external (PostgreSQL) name is FCUR. PostgreSQL knows that the FCUR function can be used to implicitly convert a FLOAT4 (or REAL) value into a FCUR value. PostgreSQL considers a function to be a conversion function if all the following are true:
The name of the function is the same as the name of a data type.
The function returns a value whose type is the same as the function's name.
The function takes a single argument of some other data type.
You can see that the FCUR function meets these criteria. Let's create the FLOAT4 function along the same pattern:
movies=# CREATE OR REPLACE FUNCTION FLOAT4( FCUR ) movies-# RETURNS FLOAT4 movies-# AS 'fcur.so','fcur_to_float4' movies-# LANGUAGE 'C' movies-# WITH( ISCACHABLE, ISSTRICT ); CREATE
Now PostgreSQL knows how to convert between FLOAT4 values and FCUR values. Why is that so important? You can now use a FCUR value in any context in which a FLOAT4 value is allowed. If you haven't defined a particular function (or operator), PostgreSQL will implicitly convert the FCUR value into a FLOAT4 value and then choose the appropriate function (or operator).
CAST FunctionsStarting with PostgreSQL release 7.3, you must explicitly create CAST functions. See the documentation for the CREATE CAST command in the release 7.3 PostgreSQL Reference Manual for more information. |
For example, you have not defined a multiplication operator for your FCUR data type, but PostgreSQL knows how to multiply FLOAT4 values:
movies=# SELECT *, (val * 5) as "Result" FROM fcur_test;
pkey | val | Result
------+--------------------+------------------
1 | 1(1/???) | 5
2 | 1(0.5/???) | 10
3 | 3(1/US$) | 15
4 | 5(0.687853/GBP) | 36.3449764251709
5 | 10(7.2566/FRF) | 6.89027905464172
6 | 1(1.5702/CA$) | 3.18430781364441
7 | 1.5702(1.5702/CA$) | 5
You can now multiply FCUR values. Notice that the Result column does not contain FCUR values. PostgreSQL converted the FCUR values into FLOAT4 values and then performed the multiplication. Of course, you can cast the result back to FCUR form. Here, we use the @ (absolute value) operator to convert from FCUR to FLOAT4 form and then cast the result back into FCUR form:
movies=# SELECT *, CAST( abs(val) AS FCUR ) FROM fcur_test;
pkey | val | fcur
------+--------------------+-----------------
1 | 1(1/???) | 1(1/US$)
2 | 1(0.5/???) | 2(1/US$)
3 | 3(1/US$) | 3(1/US$)
4 | 5(0.687853/GBP) | 7.269(1/US$)
5 | 10(7.2566/FRF) | 1.37806(1/US$)
6 | 1(1.5702/CA$) | 0.636862(1/US$)
7 | 1.5702(1.5702/CA$) | 1(1/US$)
(7 rows)
Notice that all the result values have been normalized into your local currency.
