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 Functions

Starting 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.

   


PostgreSQL
PostgreSQL (2nd Edition)
ISBN: 0672327562
EAN: 2147483647
Year: 2005
Pages: 220
Authors: Korry Douglas

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