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 handjust 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 must convert them into a common currency. The normalize() function shown in Listing 6.12 does just that.
Listing 6.12. fcur.c 0(Part 4)
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, shown in Listing 6.13.
Listing 6.13. fcur.c (Part 5)
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 STRICT 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=# IMMUTABLE STRICT
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 >= (see Listing 6.14). They all follow the same pattern as the = operator: You normalize both arguments and then compare them as double values.
Listing 6.14. fcur.c (Part 6)
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=# IMMUTABLE STRICT CREATE movies=# CREATE OR REPLACE FUNCTION fcur_lt( fcur, fcur ) movies-# RETURNS boolean movies-# AS 'fcur.so' LANGUAGE 'C' movies=# IMMUTABLE STRICT CREATE movies=# CREATE OR REPLACE FUNCTION fcur_le( fcur, fcur ) movies-# RETURNS boolean movies-# AS 'fcur.so' LANGUAGE 'C' movies=# IMMUTABLE STRICT CREATE movies=# CREATE OR REPLACE FUNCTION fcur_gt( fcur, fcur ) movies-# RETURNS boolean movies-# AS 'fcur.so' LANGUAGE 'C' movies=# IMMUTABLE STRICT CREATE movies=# CREATE OR REPLACE FUNCTION fcur_ge( fcur, fcur ) movies-# RETURNS boolean movies-# AS 'fcur.so' LANGUAGE 'C' movies=# IMMUTABLE STRICT 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 (see Listing 6.15):
Listing 6.15. fcur.c (Part 7)
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 } 304
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=# IMMUTABLE STRICT 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 (see Listing 6.16) will convert FCUR values to and from REAL values. Internally, the REAL data type is known as a float4.
Listing 6.16. fcur.c (Part 8)
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.
Listing 6.17 shows the float4_to_fcur() function:
Listing 6.17. fcur.c (Part 9)
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=# IMMUTABLE STRICT CREATE
Notice that the internal (C) name for this function is float4_to_fcur(), but the external (PostgreSQL) name is FCUR. Older versions of PostgreSQL (release 7.2 or older) know 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 of the following are true:
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=# IMMUTABLE STRICT CREATE
If you're using PostgreSQL version 7.3 or later, you must explicitly tell the PostgreSQL server that FLOAT4( FCUR ) and FCUR( FLOAT4 ) are conversion functions. To create a CAST that will convert a FLOAT4 value to an FCUR value, execute the following command:
movies=# CREATE CAST( FLOAT4 AS FCUR ) WITH FUNCTION FCUR( float4 ) AS IMPLICIT;
The CREATE CAST command specified the source type (FLOAT4), the target type (FCUR) and the signature of the conversion function (FCUR( float4 )).The AS IMPLICIT clause tells PostgreSQL that it can silently convert FLOAT4 values to FCUR values whenever it needs to; you don't have to write things like CAST( 4.0 AS FCUR) once you've defined an IMPLICIT CAST.
Don't forget to create a CAST that will convert values in the other direction:
movies=# CREATE CAST( FCUR AS FLOAT4) WITH FUNCTION FLOAT4( FCUR ) AS IMPLICIT;
Now PostgreSQL knows how to (implicitly) 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).
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.
Part I: General PostgreSQL Use
Introduction to PostgreSQL and SQL
Working with Data in PostgreSQL
PostgreSQL SQL Syntax and Use
Performance
Part II: Programming with PostgreSQL
Introduction to PostgreSQL Programming
Extending PostgreSQL
PL/pgSQL
The PostgreSQL C APIlibpq
A Simpler C APIlibpgeasy
The New PostgreSQL C++ APIlibpqxx
Embedding SQL Commands in C Programsecpg
Using PostgreSQL from an ODBC Client Application
Using PostgreSQL from a Java Client Application
Using PostgreSQL with Perl
Using PostgreSQL with PHP
Using PostgreSQL with Tcl and Tcl/Tk
Using PostgreSQL with Python
Npgsql: The .NET Data Provider
Other Useful Programming Tools
Part III: PostgreSQL Administration
Introduction to PostgreSQL Administration
PostgreSQL Administration
Internationalization and Localization
Security
Replicating PostgreSQL Data with Slony
Contributed Modules
Index