Introduction to PostgreSQL/PLJava, part 5: Operations and Indexes

Bear Giles | January 4, 2012

This article discusses operators and indexes in PL/Java. Many user-defined types will not need the former but a hash index should be supported for their use in SQL join clauses.

Operators

Operators are normal PL/Java methods that are also marked as operators via the CREATE OPERATOR statement.

Basic arithmetic for rational numbers is supported as

  1.     public static Rational negate(Rational p) throws SQLException {
  2.         if (p == null) {
  3.             return null;
  4.         }
  5.         return new Rational(-p.getNumerator(), p.getDenominator());
  6.     }
  7.  
  8.     public static Rational add(Rational p, Rational q) throws SQLException {
  9.         if ((p == null) || (q == null)) {
  10.             return null;
  11.         }
  12.         BigInteger n = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator())).add(
  13.                 BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator())));
  14.         BigInteger d = BigInteger.valueOf(p.getDenominator()).multiply(BigInteger.valueOf(q.getDenominator()));
  15.         BigInteger gcd = n.gcd(d);
  16.         n = n.divide(gcd);
  17.         d = d.divide(gcd);
  18.         return new Rational(n.longValue(), d.longValue());
  19.     }
  20.  
  21.     public static Rational subtract(Rational p, Rational q) throws SQLException {
  22.         if ((p == null) || (q == null)) {
  23.             return null;
  24.         }
  25.         BigInteger n = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator())).subtract(
  26.                 BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator())));
  27.         BigInteger d = BigInteger.valueOf(p.getDenominator()).multiply(BigInteger.valueOf(q.getDenominator()));
  28.         BigInteger gcd = n.gcd(d);
  29.         n = n.divide(gcd);
  30.         d = d.divide(gcd);
  31.         return new Rational(n.longValue(), d.longValue());
  32.     }
  33.    
  34.     public static Rational multiply(Rational p, Rational q) throws SQLException {
  35.         if ((p == null) || (q == null)) {
  36.             return null;
  37.         }
  38.         BigInteger n = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getNumerator()));
  39.         BigInteger d = BigInteger.valueOf(p.getDenominator()).multiply(BigInteger.valueOf(q.getDenominator()));
  40.         BigInteger gcd = n.gcd(d);
  41.         n = n.divide(gcd);
  42.         d = d.divide(gcd);
  43.         return new Rational(n.longValue(), d.longValue());
  44.     }
    public static Rational negate(Rational p) throws SQLException {
        if (p == null) {
            return null;
        }
        return new Rational(-p.getNumerator(), p.getDenominator());
    }

    public static Rational add(Rational p, Rational q) throws SQLException {
        if ((p == null) || (q == null)) {
            return null;
        }
        BigInteger n = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator())).add(
                BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator())));
        BigInteger d = BigInteger.valueOf(p.getDenominator()).multiply(BigInteger.valueOf(q.getDenominator()));
        BigInteger gcd = n.gcd(d);
        n = n.divide(gcd);
        d = d.divide(gcd);
        return new Rational(n.longValue(), d.longValue());
    }

    public static Rational subtract(Rational p, Rational q) throws SQLException {
        if ((p == null) || (q == null)) {
            return null;
        }
        BigInteger n = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator())).subtract(
                BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator())));
        BigInteger d = BigInteger.valueOf(p.getDenominator()).multiply(BigInteger.valueOf(q.getDenominator()));
        BigInteger gcd = n.gcd(d);
        n = n.divide(gcd);
        d = d.divide(gcd);
        return new Rational(n.longValue(), d.longValue());
    }
    
    public static Rational multiply(Rational p, Rational q) throws SQLException {
        if ((p == null) || (q == null)) {
            return null;
        }
        BigInteger n = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getNumerator()));
        BigInteger d = BigInteger.valueOf(p.getDenominator()).multiply(BigInteger.valueOf(q.getDenominator()));
        BigInteger gcd = n.gcd(d);
        n = n.divide(gcd);
        d = d.divide(gcd);
        return new Rational(n.longValue(), d.longValue());
    }

and

  1.       CREATE FUNCTION javatest.rational_negate(javatest.rational) RETURNS javatest.rational
  2.           AS 'sandbox.Rational.negate'
  3.           LANGUAGE JAVA IMMUTABLE STRICT;
  4.  
  5.       CREATE FUNCTION javatest.rational_add(javatest.rational, javatest.rational)
  6.           RETURNS javatest.rational
  7.           AS 'sandbox.Rational.add'
  8.           LANGUAGE JAVA IMMUTABLE STRICT;
  9.  
  10.       CREATE FUNCTION javatest.rational_subtract(javatest.rational, javatest.rational)
  11.           RETURNS javatest.rational
  12.           AS 'sandbox.Rational.subtract'
  13.           LANGUAGE JAVA IMMUTABLE STRICT;
  14.          
  15.       CREATE FUNCTION javatest.rational_multiply(javatest.rational, javatest.rational)
  16.           RETURNS javatest.rational
  17.           AS 'sandbox.Rational.multiply'
  18.           LANGUAGE JAVA IMMUTABLE STRICT;
  19.          
  20.       CREATE FUNCTION javatest.rational_divide(javatest.rational, javatest.rational)
  21.           RETURNS javatest.rational
  22.           AS 'sandbox.Rational.divide'
  23.           LANGUAGE JAVA IMMUTABLE STRICT;
  24.  
  25.       CREATE OPERATOR - (
  26.          rightarg = javatest.rational, procedure.rational_negate
  27.       );
  28.  
  29.       CREATE OPERATOR + (
  30.          leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_add,
  31.          commutator = +
  32.       );
  33.  
  34.       CREATE OPERATOR - (
  35.          leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_subtract
  36.       );
  37.  
  38.       CREATE OPERATOR * (
  39.          leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_divide,
  40.          commutator = *
  41.       );
  42.  
  43.       CREATE OPERATOR / (
  44.          leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_divide
  45.       );
      CREATE FUNCTION javatest.rational_negate(javatest.rational) RETURNS javatest.rational
          AS 'sandbox.Rational.negate'
          LANGUAGE JAVA IMMUTABLE STRICT;

      CREATE FUNCTION javatest.rational_add(javatest.rational, javatest.rational)
          RETURNS javatest.rational
          AS 'sandbox.Rational.add'
          LANGUAGE JAVA IMMUTABLE STRICT;

      CREATE FUNCTION javatest.rational_subtract(javatest.rational, javatest.rational)
          RETURNS javatest.rational
          AS 'sandbox.Rational.subtract'
          LANGUAGE JAVA IMMUTABLE STRICT;
          
      CREATE FUNCTION javatest.rational_multiply(javatest.rational, javatest.rational)
          RETURNS javatest.rational
          AS 'sandbox.Rational.multiply'
          LANGUAGE JAVA IMMUTABLE STRICT;
          
      CREATE FUNCTION javatest.rational_divide(javatest.rational, javatest.rational)
          RETURNS javatest.rational
          AS 'sandbox.Rational.divide'
          LANGUAGE JAVA IMMUTABLE STRICT;

      CREATE OPERATOR - (
         rightarg = javatest.rational, procedure.rational_negate
      );

      CREATE OPERATOR + (
         leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_add,
         commutator = + 
      );

      CREATE OPERATOR - (
         leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_subtract
      );

      CREATE OPERATOR * (
         leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_divide,
         commutator = *
      );

      CREATE OPERATOR / (
         leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_divide
      );

The operator characters are one to 63 characters from the set “+ – * / < > = ~ ! @ # % ^ & | ` ?” with a few restrictions to avoid confusion with the start of SQL comments.

The commutator operator is a second operator (possibly the same) that has the same results if the left and right values are swapped. This is used by the optimizer.

The negator operator is one that the opposite results if the left and right values are swapped. It is only valid on procedures that return a boolean value. Again this is used by the optimizer.

Ordering Operators

Many UDTs can be ordered in some manner. This may be something obvious, e.g., ordering rational numbers, or something a bit more arbitrary, e.g., ordering complex numbers.

We can define ordering operations in the same manner as above. N.B., there is no longer anything special about these operators – with an unfamiliar UDT you can’t assume that < really means “less than”. The sole exception is “!=” which is always rewritten as “” by the parser.

  1.     public static int compare(Rational p, Rational q) {
  2.         if (p == null) {
  3.             return 1;
  4.         } else if (q == null) {
  5.             return -1;
  6.         }
  7.         BigInteger l = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator()));
  8.         BigInteger r = BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator()));
  9.         return l.compareTo(r);
  10.     }
  11.    
  12.     public int compareTo(Rational p) {
  13.         return compare(this, p);
  14.     }
  15.  
  16.     public static int compare(Rational p, double q) {
  17.         if (p == null) {
  18.             return 1;
  19.         }
  20.         double d = p.doubleValue();
  21.         return (d < q) ? -1 : ((d == q) ? 0 : 1);
  22.     }
  23.    
  24.     public int compareTo(double q) {
  25.         return compare(this, q);
  26.     }
  27.  
  28.     public static boolean lessThan(Rational p, Rational q) {
  29.         return compare(p, q) < 0;
  30.     }
  31.    
  32.     public static boolean lessThanOrEquals(Rational p, Rational q) {
  33.         return compare(p, q) <= 0;
  34.     }
  35.        
  36.     public static boolean equals(Rational p, Rational q) {
  37.         return compare(p, q) = 0;
  38.     }
  39.  
  40.     public static boolean greaterThan(Rational p, Rational q) {
  41.         return compare(p, q) > 0;
  42.     }
  43.        
  44.     public static boolean lessThan(Rational p, double q) {
  45.         if (p == null) {
  46.             return false;
  47.         }
  48.         return p.compareTo(q) < 0;
  49.     }
  50.    
  51.     public static boolean lessThanOrEquals(Rational p, double q) {
  52.         if (p == null) {
  53.             return false;
  54.         }
  55.         return p.compareTo(q) = 0;
  56.     }
  57.    
  58.     public static boolean greaterThan(Rational p, double q) {
  59.         if (p == null) {
  60.             return true;
  61.         }
  62.         return p.compareTo(q) > 0;
  63.     }
    public static int compare(Rational p, Rational q) {
        if (p == null) {
            return 1;
        } else if (q == null) {
            return -1;
        }
        BigInteger l = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator()));
        BigInteger r = BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator()));
        return l.compareTo(r);
    }
    
    public int compareTo(Rational p) {
        return compare(this, p);
    }

    public static int compare(Rational p, double q) {
        if (p == null) {
            return 1;
        }
        double d = p.doubleValue();
        return (d < q) ? -1 : ((d == q) ? 0 : 1);
    }
    
    public int compareTo(double q) {
        return compare(this, q);
    }

    public static boolean lessThan(Rational p, Rational q) {
        return compare(p, q) < 0;
    }
    
    public static boolean lessThanOrEquals(Rational p, Rational q) {
        return compare(p, q) <= 0;
    }
        
    public static boolean equals(Rational p, Rational q) {
        return compare(p, q) = 0;
    }

    public static boolean greaterThan(Rational p, Rational q) {
        return compare(p, q) > 0;
    }
        
    public static boolean lessThan(Rational p, double q) {
        if (p == null) {
            return false;
        }
        return p.compareTo(q) < 0;
    }
    
    public static boolean lessThanOrEquals(Rational p, double q) {
        if (p == null) {
            return false;
        }
        return p.compareTo(q) = 0;
    }
    
    public static boolean greaterThan(Rational p, double q) {
        if (p == null) {
            return true;
        }
        return p.compareTo(q) > 0;
    }

Note that I’ve defined methods to compare either two rational numbers or one rational number and one double number.

  1.       CREATE FUNCTION javatest.rational_lt(javatest.rational, javatest.rational)
  2.           RETURNS bool
  3.           AS 'sandbox.Rational.lessThan'
  4.           LANGUAGE JAVA IMMUTABLE STRICT;
  5.  
  6.       CREATE FUNCTION javatest.rational_le(javatest.rational, javatest.rational)
  7.           RETURNS bool
  8.           AS 'sandbox.Rational.lessThanOrEquals'
  9.           LANGUAGE JAVA IMMUTABLE STRICT;
  10.  
  11.       CREATE FUNCTION javatest.rational_eq(javatest.rational, javatest.rational)
  12.           RETURNS bool
  13.           AS 'sandbox.Rational.equals'
  14.           LANGUAGE JAVA IMMUTABLE STRICT;
  15.      
  16.       CREATE FUNCTION javatest.rational_ge(javatest.rational, javatest.rational)
  17.           RETURNS bool
  18.           AS 'sandbox.Rational.greaterThanOrEquals'
  19.           LANGUAGE JAVA IMMUTABLE STRICT;
  20.      
  21.       CREATE FUNCTION javatest.rational_gt(javatest.rational, javatest.rational)
  22.           RETURNS bool
  23.           AS 'sandbox.Rational.greaterThan'
  24.           LANGUAGE JAVA IMMUTABLE STRICT;
  25.  
  26.       CREATE FUNCTION javatest.rational_cmp(javatest.rational, javatest.rational)
  27.           RETURNS int
  28.           AS 'sandbox.Rational.compare'
  29.           LANGUAGE JAVA IMMUTABLE STRICT;
  30.  
  31.       CREATE FUNCTION javatest.rational_lt(javatest.rational, float8)
  32.           RETURNS bool
  33.           AS 'sandbox.Rational.lessThan'
  34.           LANGUAGE JAVA IMMUTABLE STRICT;
  35.  
  36.       CREATE FUNCTION javatest.rational_le(javatest.rational, float8)
  37.           RETURNS bool
  38.           AS 'sandbox.Rational.lessThanOrEquals'
  39.           LANGUAGE JAVA IMMUTABLE STRICT;
  40.  
  41.       CREATE FUNCTION javatest.rational_eq(javatest.rational, float8)
  42.           RETURNS bool
  43.           AS 'sandbox.Rational.equals'
  44.           LANGUAGE JAVA IMMUTABLE STRICT;
  45.  
  46.       CREATE FUNCTION javatest.rational_ge(javatest.rational, float8)
  47.           RETURNS bool
  48.           AS 'sandbox.Rational.greaterThanOrEquals'
  49.           LANGUAGE JAVA IMMUTABLE STRICT;
  50.  
  51.       CREATE FUNCTION javatest.rational_gt(javatest.rational, float8)
  52.           RETURNS bool
  53.           AS 'sandbox.Rational.greaterThan'
  54.           LANGUAGE JAVA IMMUTABLE STRICT;
  55.  
  56.       CREATE OPERATOR < (
  57.          leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_lt,
  58.          commutator = > , negator = >= ,
  59.          restrict = scalarltsel, join = scalarltjoinsel, merges
  60.       );
  61.  
  62.       CREATE OPERATOR <= (
  63.          leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_le,
  64.          commutator = >= , negator = > ,
  65.          restrict = scalarltsel, join = scalarltjoinsel, merges
  66.       );
  67.  
  68.       CREATE OPERATOR = (
  69.          leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_eq,
  70.          commutator = = , negator = <>, hashes, merges
  71.       );
  72.  
  73.       CREATE OPERATOR >= (
  74.          leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_lt,
  75.          commutator = <= , negator = < ,
  76.          restrict = scalarltsel, join = scalarltjoinsel, merges
  77.       );
  78.  
  79.       CREATE OPERATOR > (
  80.          leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_le,
  81.          commutator = <= , negator = < ,
  82.          restrict = scalargtsel, join = scalargtjoinsel, merges
  83.       );
  84.  
  85.       CREATE OPERATOR < (
  86.          leftarg = javatest.rational, rightarg = float8, procedure = javatest.rational_lt,
  87.          commutator = > , negator = >=
  88.       );
  89.  
  90.       CREATE OPERATOR <= (
  91.          leftarg = javatest.rational, rightarg = float8, procedure = javatest.rational_le,
  92.          commutator = >= , negator = >
  93.       );
  94.  
  95.       CREATE OPERATOR = (
  96.          leftarg = javatest.rational, rightarg = float8, procedure = javatest.rational_eq,
  97.          commutator = = , negator = <>
  98.       );
  99.  
  100.       CREATE OPERATOR >= (
  101.          leftarg = javatest.rational, rightarg = float8, procedure = javatest.rational_ge,
  102.          commutator = <= , negator = <
  103.       );
  104.  
  105.       CREATE OPERATOR > (
  106.          leftarg = javatest.rational, rightarg = float8, procedure = javatest.rational_gt,
  107.          commutator = < , negator = <=
  108.       );
      CREATE FUNCTION javatest.rational_lt(javatest.rational, javatest.rational)
          RETURNS bool
          AS 'sandbox.Rational.lessThan'
          LANGUAGE JAVA IMMUTABLE STRICT;

      CREATE FUNCTION javatest.rational_le(javatest.rational, javatest.rational)
          RETURNS bool
          AS 'sandbox.Rational.lessThanOrEquals'
          LANGUAGE JAVA IMMUTABLE STRICT;

      CREATE FUNCTION javatest.rational_eq(javatest.rational, javatest.rational)
          RETURNS bool
          AS 'sandbox.Rational.equals'
          LANGUAGE JAVA IMMUTABLE STRICT;
      
      CREATE FUNCTION javatest.rational_ge(javatest.rational, javatest.rational)
          RETURNS bool
          AS 'sandbox.Rational.greaterThanOrEquals'
          LANGUAGE JAVA IMMUTABLE STRICT;
      
      CREATE FUNCTION javatest.rational_gt(javatest.rational, javatest.rational)
          RETURNS bool
          AS 'sandbox.Rational.greaterThan'
          LANGUAGE JAVA IMMUTABLE STRICT;

      CREATE FUNCTION javatest.rational_cmp(javatest.rational, javatest.rational)
          RETURNS int
          AS 'sandbox.Rational.compare'
          LANGUAGE JAVA IMMUTABLE STRICT;

      CREATE FUNCTION javatest.rational_lt(javatest.rational, float8)
          RETURNS bool
          AS 'sandbox.Rational.lessThan'
          LANGUAGE JAVA IMMUTABLE STRICT;

      CREATE FUNCTION javatest.rational_le(javatest.rational, float8)
          RETURNS bool
          AS 'sandbox.Rational.lessThanOrEquals'
          LANGUAGE JAVA IMMUTABLE STRICT;

      CREATE FUNCTION javatest.rational_eq(javatest.rational, float8)
          RETURNS bool
          AS 'sandbox.Rational.equals'
          LANGUAGE JAVA IMMUTABLE STRICT;

      CREATE FUNCTION javatest.rational_ge(javatest.rational, float8)
          RETURNS bool
          AS 'sandbox.Rational.greaterThanOrEquals'
          LANGUAGE JAVA IMMUTABLE STRICT;

      CREATE FUNCTION javatest.rational_gt(javatest.rational, float8)
          RETURNS bool
          AS 'sandbox.Rational.greaterThan'
          LANGUAGE JAVA IMMUTABLE STRICT;

      CREATE OPERATOR < (
         leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_lt,
         commutator = > , negator = >= ,
         restrict = scalarltsel, join = scalarltjoinsel, merges
      );

      CREATE OPERATOR <= (
         leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_le,
         commutator = >= , negator = > , 
         restrict = scalarltsel, join = scalarltjoinsel, merges
      );

      CREATE OPERATOR = (
         leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_eq,
         commutator = = , negator = <>, hashes, merges
      );

      CREATE OPERATOR >= (
         leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_lt,
         commutator = <= , negator = < ,
         restrict = scalarltsel, join = scalarltjoinsel, merges
      );

      CREATE OPERATOR > (
         leftarg = javatest.rational, rightarg = javatest.rational, procedure = javatest.rational_le,
         commutator = <= , negator = < , 
         restrict = scalargtsel, join = scalargtjoinsel, merges
      );

      CREATE OPERATOR < (
         leftarg = javatest.rational, rightarg = float8, procedure = javatest.rational_lt,
         commutator = > , negator = >= 
      );

      CREATE OPERATOR <= (
         leftarg = javatest.rational, rightarg = float8, procedure = javatest.rational_le,
         commutator = >= , negator = > 
      );

      CREATE OPERATOR = (
         leftarg = javatest.rational, rightarg = float8, procedure = javatest.rational_eq,
         commutator = = , negator = <> 
      );

      CREATE OPERATOR >= (
         leftarg = javatest.rational, rightarg = float8, procedure = javatest.rational_ge,
         commutator = <= , negator = <
      );

      CREATE OPERATOR > (
         leftarg = javatest.rational, rightarg = float8, procedure = javatest.rational_gt,
         commutator = < , negator = <=
      );

Restrict is an optimization estimator procedure. It’s usually safe to use the appropriate standard procedure.

Join is an optimization estimator procedure. It’s usually safe to use the appropriate standard procedure.

Hashes indicates that the operator can be used in hash joins.

Merges indicates that the operator can be used in merge joins.

Indexes

Indexes are used in three places – to enforce uniqueness constraints and to speed up WHERE and JOIN clauses.

  1.     -- btree join
  2.   CREATE OPERATOR CLASS rational_ops
  3.       DEFAULT FOR TYPE javatest.rational USING btree AS
  4.         OPERATOR        1       < ,
  5.         OPERATOR        2       <= ,
  6.         OPERATOR        3       = ,
  7.         OPERATOR        4       >= ,
  8.         OPERATOR        5       > ,
  9.         FUNCTION        1       javatest.rational_cmp(javatest.rational, javatest.rational);
  10.  
  11.     -- hash join
  12.    CREATE OPERATOR CLASS rational_ops
  13.       DEFAULT FOR TYPE javatest.rational USING hash AS
  14.         OPERATOR        1       = ,
  15.         FUNCTION        1       javatest.rational_hashCode(javatest.rational);
    -- btree join
  CREATE OPERATOR CLASS rational_ops
      DEFAULT FOR TYPE javatest.rational USING btree AS
        OPERATOR        1       < ,
        OPERATOR        2       <= ,
        OPERATOR        3       = ,
        OPERATOR        4       >= ,
        OPERATOR        5       > ,
        FUNCTION        1       javatest.rational_cmp(javatest.rational, javatest.rational);

    -- hash join
   CREATE OPERATOR CLASS rational_ops
      DEFAULT FOR TYPE javatest.rational USING hash AS
        OPERATOR        1       = ,
        FUNCTION        1       javatest.rational_hashCode(javatest.rational);

Operator Families

Finally, PostgreSQL has the concept of “Operator Families” that group related operator classes under a single umbrella. For instance you might have one family that supports cross-comparison between int2, int4 and int8 values. Each can be specified individually but by creating an operator family you give a few more hints to the PostgreSQL optimizer.

More Information

CREATE OPERATOR (PostgreSQL)

CREATE OPERATOR CLASS (PostgreSQL)

CREATE OPERATOR FAMILY (PostgreSQL)

Operator Optimization (PostgreSQL)

Interfacing Extensions To Indexes (PostreSQL)

Comments
No Comments »
Categories
java, pl/java, PostgreSQL, user-defined types
Comments rss Comments rss
Trackback Trackback

Introduction to PostgreSQL PL/Java, part 4: User Defined Types

Bear Giles | January 2, 2012

User-defined types in the database are controversial. They’re not standard – at some point the DBA has to create them – and this introduces portability issues. Standard tools won’t know about them. You must access them via the ‘struct’ methods in ResultSets and PreparedStatements.

On the other hand there are a LOT of things that are otherwise only supported as byte[]. This prevents database functions and stored procedures from easily manipulating them.

What would be a good user-defined type? It must be atomic and it must be possible to do meaningful work via stored procedures. N.B., a database user-defined type is not the same thing as a java class. Nearly all java classes should be stored as standard tuples and you should only use database UDTs if there’s a compelling reason.

A touchstone I like is asking whether you’re ever tempted to cache immutable information about the type, vs. about the tuple, in addition to the object itself. E.g., a X.509 digital certificate has a number of immutable fields that would be valid search terms but it’s expensive to extract that information for every row. (Sidenote: you can use triggers to extract the information when the record is inserted and updated. This ensures the cached values are always accurate.)

Examples:

  • complex numbers (stored procedures: arithmetic)
  • rational numbers (stored procedures: arithmetic)
  • galois field numbers (stored procedures: arithmetic modulo a fixed value)
  • images (stored procedures: get dimensions)
  • PDF documents (stored procedures: extract elements)
  • digital certificates and private keys (stored procedures: crypto)

Something that should also be addressed is the proper language for implementation. It’s easy to prototype in PL/Java but you can make a strong argument that types should be ultimately implemented as a standard PostgreSQL extensions since they’re more likely to be available in the future when you’re looking at a 20-year-old dump. In some important ways this is just a small part of the problem – the issue isn’t whether the actual storage and function implementation is written in C or java, it’s how it’s tied into the rest of the system.

PL/Java Implementation

A PL/Java user defined type must implement the java.sql.SQLData interface, a static method that creates the object from a String, and an instance method that creates a String from the object. These methods must complementary – it must be possible to run a value through a full cycle in either direction and get the original value back.

N.B., this is often impossible with doubles – this is why you get numbers like 4.000000001 or 2.999999999. In these cases you have do to the best you can and warn the user.

In many cases an object can be stored more efficiently in a binary format. In PostgreSQL terms these are TOAST types. This is handled by implementing two new methods that work with SQLInput and SQLOutput streams.

A simple implementation of a rational type follows.

  1. public class Rational implements SQLData {
  2.     private long numerator;
  3.     private long denominator;
  4.     private String typeName;
  5.  
  6.     public static Rational parse(String input, String typeName)
  7.             throws SQLException {
  8.         Pattern pattern = Pattern.compile("(-?[0-9]+)( */ *(-?[0-9]+))?");
  9.         Matcher matcher = pattern.matcher(input);
  10.         if (!matcher.matches()) {
  11.             throw new SQLException("Unable to parse rational from string \"" + input
  12.                     + '"');
  13.         }
  14.         if (matcher.groupCount() == 3) {
  15.             if (matcher.group(3) == null) {
  16.                 return new Rational(Long.parseLong(matcher.group(1)));
  17.             }
  18.             return new Rational(Long.parseLong(matcher.group(1)),
  19.                     Long.parseLong(matcher.group(3)));
  20.         }
  21.         throw new SQLException("invalid format: \"" + input
  22.                 + '"');
  23.     }
  24.  
  25.     public Rational(long numerator) throws SQLException {
  26.         this(numerator, 1);
  27.     }
  28.  
  29.     public Rational(long numerator, long denominator) throws SQLException {
  30.         if (denominator == 0) {
  31.             throw new SQLException("demominator must be non-zero");
  32.         }
  33.  
  34.         // do a little bit of normalization
  35.         if (denominator < 0) {
  36.             numerator = -numerator;
  37.             denominator = -denominator;
  38.         }
  39.  
  40.         this.numerator = numerator;
  41.         this.denominator = denominator;
  42.     }
  43.  
  44.     public Rational(int numerator, int denominator, String typeName)
  45.             throws SQLException {
  46.         this(numerator, denominator);
  47.         this.typeName = typeName;
  48.     }
  49.  
  50.     public String getSQLTypeName() {
  51.         return typeName;
  52.     }
  53.  
  54.     public void readSQL(SQLInput stream, String typeName) throws SQLException {
  55.         this.numerator = stream.readLong();
  56.         this.denominator = stream.readLong();
  57.         this.typeName = typeName;
  58.     }
  59.  
  60.     public void writeSQL(SQLOutput stream) throws SQLException {
  61.         stream.writeLong(numerator);
  62.         stream.writeLong(denominator);
  63.     }
  64.  
  65.     public String toString() {
  66.         String value = null;
  67.         if (denominator == 1) {
  68.             value = String.valueOf(numerator);
  69.         } else {
  70.             value = String.format("%d/%d", numerator, denominator);
  71.         }
  72.         return value;
  73.     }
  74.  
  75.     /*
  76.      * Meaningful code that actually does something with this type was
  77.      * intentionally left out.
  78.      */
  79. }
public class Rational implements SQLData {
    private long numerator;
    private long denominator;
    private String typeName;

    public static Rational parse(String input, String typeName)
            throws SQLException {
        Pattern pattern = Pattern.compile("(-?[0-9]+)( */ *(-?[0-9]+))?");
        Matcher matcher = pattern.matcher(input);
        if (!matcher.matches()) {
            throw new SQLException("Unable to parse rational from string \"" + input
                    + '"');
        }
        if (matcher.groupCount() == 3) {
            if (matcher.group(3) == null) {
                return new Rational(Long.parseLong(matcher.group(1)));
            }
            return new Rational(Long.parseLong(matcher.group(1)),
                    Long.parseLong(matcher.group(3)));
        }
        throw new SQLException("invalid format: \"" + input
                + '"');
    }

    public Rational(long numerator) throws SQLException {
        this(numerator, 1);
    }

    public Rational(long numerator, long denominator) throws SQLException {
        if (denominator == 0) {
            throw new SQLException("demominator must be non-zero");
        }

        // do a little bit of normalization
        if (denominator < 0) {
            numerator = -numerator;
            denominator = -denominator;
        }

        this.numerator = numerator;
        this.denominator = denominator;
    }

    public Rational(int numerator, int denominator, String typeName)
            throws SQLException {
        this(numerator, denominator);
        this.typeName = typeName;
    }

    public String getSQLTypeName() {
        return typeName;
    }

    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        this.numerator = stream.readLong();
        this.denominator = stream.readLong();
        this.typeName = typeName;
    }

    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeLong(numerator);
        stream.writeLong(denominator);
    }

    public String toString() {
        String value = null;
        if (denominator == 1) {
            value = String.valueOf(numerator);
        } else {
            value = String.format("%d/%d", numerator, denominator);
        }
        return value;
    }

    /*
     * Meaningful code that actually does something with this type was
     * intentionally left out.
     */
}

and

  1.     /* The shell type */
  2.     CREATE TYPE javatest.rational;
  3.  
  4.     /* The scalar input function */
  5.     CREATE FUNCTION javatest.rational_in(cstring)
  6.       RETURNS javatest.rational
  7.       AS 'UDT[sandbox.Rational] input'
  8.       LANGUAGE java IMMUTABLE STRICT;
  9.  
  10.     /* The scalar output function */
  11.     CREATE FUNCTION javatest.rational_out(javatest.rational)
  12.       RETURNS cstring
  13.       AS 'UDT[sandbox.Rational] output'
  14.       LANGUAGE java IMMUTABLE STRICT;
  15.  
  16.     /* The scalar receive function */
  17.     CREATE FUNCTION javatest.rational_recv(internal)
  18.       RETURNS javatest.rational
  19.       AS 'UDT[sandbox.Rational] receive'
  20.       LANGUAGE java IMMUTABLE STRICT;
  21.  
  22.     /* The scalar send function */
  23.     CREATE FUNCTION javatest.rational_send(javatest.rational)
  24.       RETURNS bytea
  25.       AS 'UDT[sandbox.Rational] send'
  26.       LANGUAGE java IMMUTABLE STRICT;
  27.  
  28.     CREATE TYPE javatest.rational (
  29.       internallength = 16,
  30.       input = javatest.rational_in,
  31.       output = javatest.rational_out,
  32.       receive = javatest.rational_recv,
  33.       send = javatest.rational_send,
  34.       alignment = int);
    /* The shell type */
    CREATE TYPE javatest.rational;

    /* The scalar input function */
    CREATE FUNCTION javatest.rational_in(cstring)
      RETURNS javatest.rational
      AS 'UDT[sandbox.Rational] input'
      LANGUAGE java IMMUTABLE STRICT;

    /* The scalar output function */
    CREATE FUNCTION javatest.rational_out(javatest.rational)
      RETURNS cstring
      AS 'UDT[sandbox.Rational] output'
      LANGUAGE java IMMUTABLE STRICT;

    /* The scalar receive function */
    CREATE FUNCTION javatest.rational_recv(internal)
      RETURNS javatest.rational
      AS 'UDT[sandbox.Rational] receive'
      LANGUAGE java IMMUTABLE STRICT;

    /* The scalar send function */
    CREATE FUNCTION javatest.rational_send(javatest.rational)
      RETURNS bytea
      AS 'UDT[sandbox.Rational] send'
      LANGUAGE java IMMUTABLE STRICT;

    CREATE TYPE javatest.rational (
      internallength = 16,
      input = javatest.rational_in,
      output = javatest.rational_out,
      receive = javatest.rational_recv,
      send = javatest.rational_send,
      alignment = int);

Type modifiers

PostgreSQL allows types to have modifiers. Examples are in ‘varchar(200)’ or ‘numeric(8,2)’.

PL/Java does not currently support this functionality (via the ‘typmod_in’ and ‘typmod_out’ methods) but I have submitted a request for it.

Casts

Custom types aren’t particularly useful if all you can do is store and retrieve the values as opaque objects. Why not use bytea and be done with it?

In fact there are many UDTs where it makes sense to be able to cast a UDT to a different type. Numeric types, like complex or rational numbers, should be able to be converted to and from the standard integer and floating number types (albeit with limitations).

This should be done with restraint.

Casts are implemented as single argument static methods. In the java world these methods are often named newInstance so I’m doing the same here.

  1.     public static Rational newInstance(String input) throws SQLException {
  2.         if (input == null) {
  3.             return null;
  4.         }
  5.         return parse(input, "javatest.rational");
  6.     }
  7.  
  8.     public static Rational newInstance(int value) throws SQLException {
  9.         return new Rational(value);
  10.     }
  11.  
  12.     public static Rational newInstance(Integer value) throws SQLException {
  13.         if (value == null) {
  14.             return null;
  15.         }
  16.         return new Rational(value.intValue());
  17.     }
  18.  
  19.     public static Rational newInstance(long value) throws SQLException {
  20.         return new Rational(value);
  21.     }
  22.  
  23.     public static Rational newInstance(Long value) throws SQLException {
  24.         if (value == null) {
  25.             return null;
  26.         }
  27.         return new Rational(value.longValue());
  28.     }
  29.  
  30.     public static Double value(Rational value) throws SQLException {
  31.         if (value == null) {
  32.             return null;
  33.         }
  34.         return value.doubleValue();
  35.     }
    public static Rational newInstance(String input) throws SQLException {
        if (input == null) {
            return null;
        }
        return parse(input, "javatest.rational");
    }

    public static Rational newInstance(int value) throws SQLException {
        return new Rational(value);
    }

    public static Rational newInstance(Integer value) throws SQLException {
        if (value == null) {
            return null;
        }
        return new Rational(value.intValue());
    }

    public static Rational newInstance(long value) throws SQLException {
        return new Rational(value);
    }

    public static Rational newInstance(Long value) throws SQLException {
        if (value == null) {
            return null;
        }
        return new Rational(value.longValue());
    }

    public static Double value(Rational value) throws SQLException {
        if (value == null) {
            return null;
        }
        return value.doubleValue();
    }

and

  1.   CREATE FUNCTION javatest.rational_string_as_rational(varchar) RETURNS javatest.rational
  2.         AS 'sandbox.Rational.newInstance'
  3.         LANGUAGE JAVA IMMUTABLE STRICT;
  4.  
  5.   CREATE FUNCTION javatest.rational_int_as_rational(int4) RETURNS javatest.rational
  6.         AS 'sandbox.Rational.newInstance'
  7.         LANGUAGE JAVA IMMUTABLE STRICT;
  8.  
  9.   CREATE FUNCTION javatest.rational_long_as_rational(int8) RETURNS javatest.rational
  10.         AS 'sandbox.Rational.newInstance'
  11.         LANGUAGE JAVA IMMUTABLE STRICT;
  12.  
  13.   CREATE FUNCTION javatest.rational_as_double(javatest.rational) RETURNS float8
  14.         AS 'sandbox.Rational.value'
  15.         LANGUAGE JAVA IMMUTABLE STRICT;
  16.  
  17.   CREATE CAST (varchar AS javatest.rational)
  18.       WITH FUNCTION javatest.rational_string_as_rational(varchar)
  19.       AS ASSIGNMENT;
  20.  
  21.   CREATE CAST (int4 AS javatest.rational)
  22.       WITH FUNCTION javatest.rational_int_as_rational(int4)
  23.       AS ASSIGNMENT;
  24.  
  25.   CREATE CAST (int8 AS javatest.rational)
  26.       WITH FUNCTION javatest.rational_long_as_rational(int8)
  27.       AS ASSIGNMENT;
  28.  
  29.   CREATE CAST (javatest.rational AS float8)
  30.       WITH FUNCTION javatest.rational_as_double(javatest.rational)
  31.       AS ASSIGNMENT;
  CREATE FUNCTION javatest.rational_string_as_rational(varchar) RETURNS javatest.rational
        AS 'sandbox.Rational.newInstance'
        LANGUAGE JAVA IMMUTABLE STRICT;

  CREATE FUNCTION javatest.rational_int_as_rational(int4) RETURNS javatest.rational
        AS 'sandbox.Rational.newInstance'
        LANGUAGE JAVA IMMUTABLE STRICT;

  CREATE FUNCTION javatest.rational_long_as_rational(int8) RETURNS javatest.rational
        AS 'sandbox.Rational.newInstance'
        LANGUAGE JAVA IMMUTABLE STRICT;

  CREATE FUNCTION javatest.rational_as_double(javatest.rational) RETURNS float8
        AS 'sandbox.Rational.value'
        LANGUAGE JAVA IMMUTABLE STRICT;

  CREATE CAST (varchar AS javatest.rational)
      WITH FUNCTION javatest.rational_string_as_rational(varchar)
      AS ASSIGNMENT;

  CREATE CAST (int4 AS javatest.rational)
      WITH FUNCTION javatest.rational_int_as_rational(int4)
      AS ASSIGNMENT;

  CREATE CAST (int8 AS javatest.rational)
      WITH FUNCTION javatest.rational_long_as_rational(int8)
      AS ASSIGNMENT;

  CREATE CAST (javatest.rational AS float8)
      WITH FUNCTION javatest.rational_as_double(javatest.rational)
      AS ASSIGNMENT;

(Sidenote: STRICT means that the function will return NULL if any argument is NULL. This allows the database to make some optimizations.)

(Sidenote: we may only be able to use the IMMUTABLE flag if the java objects are also immutable. We should probably make our Rational objects immutable since the other numeric types are immutable.)

Aggregate Functions

What about min()? Rational numbers are a numeric type so shouldn’t they support all of the standard aggregate functions?

Defining new aggregate functions is straightforward. Simple aggregate functions only need a static member function that take two UDT values and return one. This is easy to see with maximums, minimums, sums, products, etc. More complex aggregates require an ancillary UDT that contains state information, a static method that takes one state UDT and one UDT and returns a state UDT, and a finalization method that takes the final state UDT and produces the results. This is easy to see with averages – you need a state type that contains a counter and a running sum.

Several examples of the former type of aggregate function follow.

  1.    
  2.     // compare two Rational objects. We use BigInteger to avoid overflow.
  3.     public static int compare(Rational p, Rational q) {
  4.         if (p == null) {
  5.             return 1;
  6.         } else if (q == null) {
  7.             return -1;
  8.         }
  9.         BigInteger l = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator()));
  10.         BigInteger r = BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator()));
  11.         return l.compareTo(r);
  12.     }
  13.  
  14.     public static Rational min(Rational p, Rational q) {
  15.         if ((p == null) || (q == null)) {
  16.             return null;
  17.         }
  18.         return (p.compareTo(q) <= 0) ? p : q;
  19.     }
  20.    
  21.     public static Rational max(Rational p, Rational q) {
  22.         if ((p == null) || (q == null)) {
  23.             return null;
  24.         }
  25.         return (q.compareTo(p) < 0) ? p : q;
  26.     }
  27.  
  28.     public static Rational add(Rational p, Rational q) throws SQLException {
  29.         if ((p == null) || (q == null)) {
  30.             return null;
  31.         }
  32.         BigInteger n = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator())).add(
  33.                 BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator())));
  34.         BigInteger d = BigInteger.valueOf(p.getDenominator()).multiply(BigInteger.valueOf(q.getDenominator()));
  35.         BigInteger gcd = n.gcd(d);
  36.         n = n.divide(gcd);
  37.         d = d.divide(gcd);
  38.         return new Rational(n.longValue(), d.longValue());
  39.     }
   
    // compare two Rational objects. We use BigInteger to avoid overflow.
    public static int compare(Rational p, Rational q) {
        if (p == null) {
            return 1;
        } else if (q == null) {
            return -1;
        }
        BigInteger l = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator()));
        BigInteger r = BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator()));
        return l.compareTo(r);
    }

    public static Rational min(Rational p, Rational q) {
        if ((p == null) || (q == null)) {
            return null;
        }
        return (p.compareTo(q) <= 0) ? p : q;
    }
    
    public static Rational max(Rational p, Rational q) {
        if ((p == null) || (q == null)) {
            return null;
        }
        return (q.compareTo(p) < 0) ? p : q;
    }

    public static Rational add(Rational p, Rational q) throws SQLException {
        if ((p == null) || (q == null)) {
            return null;
        }
        BigInteger n = BigInteger.valueOf(p.getNumerator()).multiply(BigInteger.valueOf(q.getDenominator())).add(
                BigInteger.valueOf(q.getNumerator()).multiply(BigInteger.valueOf(p.getDenominator())));
        BigInteger d = BigInteger.valueOf(p.getDenominator()).multiply(BigInteger.valueOf(q.getDenominator()));
        BigInteger gcd = n.gcd(d);
        n = n.divide(gcd);
        d = d.divide(gcd);
        return new Rational(n.longValue(), d.longValue());
    }

and

  1.     CREATE FUNCTION javatest.min(javatest.rational, javatest.rational) RETURNS javatest.rational
  2.         AS 'sandbox.Rational.min'
  3.         LANGUAGE JAVA IMMUTABLE STRICT;
  4.  
  5.     CREATE FUNCTION javatest.max(javatest.rational, javatest.rational) RETURNS javatest.rational
  6.         AS 'sandbox.Rational.max'
  7.         LANGUAGE JAVA IMMUTABLE STRICT;
  8.  
  9.     CREATE AGGREGATE min(javatest.rational) (
  10.       sfunc = javatest.min,
  11.       stype = javatest.rational
  12.     );
  13.  
  14.     CREATE AGGREGATE max(javatest.rational) (
  15.       sfunc = javatest.max,
  16.       stype = javatest.rational
  17.     );
  18.  
  19.     CREATE AGGREGATE sum(javatest.rational) (
  20.       sfunc = javatest.add,
  21.       stype = javatest.rational
  22.     );
    CREATE FUNCTION javatest.min(javatest.rational, javatest.rational) RETURNS javatest.rational
        AS 'sandbox.Rational.min'
        LANGUAGE JAVA IMMUTABLE STRICT;

    CREATE FUNCTION javatest.max(javatest.rational, javatest.rational) RETURNS javatest.rational
        AS 'sandbox.Rational.max'
        LANGUAGE JAVA IMMUTABLE STRICT;

    CREATE AGGREGATE min(javatest.rational) (
      sfunc = javatest.min,
      stype = javatest.rational
    );

    CREATE AGGREGATE max(javatest.rational) (
      sfunc = javatest.max,
      stype = javatest.rational
    );

    CREATE AGGREGATE sum(javatest.rational) (
      sfunc = javatest.add,
      stype = javatest.rational
    );

Integration with Hibernate

It is possible to link PL/Java user-defined types and Hibernate user-defined types. Warning: the hibernate code is highly database-specific.

This is the hibernate user-defined type. PostgreSQL 9.1 does not support the STRUCT type and uses strings instead. We don’t have to use the PL/Java user-defined data type to perform the marshaling but it ensures consistency. TheDbRationalType is the Rational class above. The same class could be used in both places but would introduce dependency on a Hibernate interface into the PL/Java class. This may be acceptable if you extract that single interface from the Hibernate source code.

  1. public class Rational implements UserType, Serializable {
  2.     private final int[] sqlTypesSupported = new int[] { Types.OTHER };
  3.     private long numerator;
  4.     private long denominator;
  5.  
  6.     public Rational() {
  7.         numerator = 0;
  8.         denominator = 1;
  9.     }
  10.  
  11.     public Rational(long numerator, long denominator) {
  12.         this.numerator = numerator;
  13.         this.denominator = denominator;
  14.     }
  15.  
  16.     public long getNumerator() {
  17.         return numerator;
  18.     }
  19.  
  20.     public long getDenominator() {
  21.         return denominator;
  22.     }
  23.  
  24.     @Override
  25.     public Object assemble(Serializable cached, Object owner)
  26.             throws HibernateException {
  27.         if (!(cached instanceof Rational)) {
  28.             throw new HibernateException("invalid argument");
  29.         }
  30.         Rational r = (Rational) cached;
  31.         return new Rational(r.getNumerator(), r.getDenominator());
  32.     }
  33.  
  34.     @Override
  35.     public Serializable disassemble(Object value) throws HibernateException {
  36.         if (!(value instanceof Rational)) {
  37.             throw new HibernateException("invalid argument");
  38.         }
  39.         return (Rational) value;
  40.     }
  41.  
  42.     @Override
  43.     public Object deepCopy(Object value) throws HibernateException {
  44.         if (value == null) {
  45.             return null
  46.         }
  47.         if (!(value instanceof Rational)) {
  48.             throw new HibernateException("invalid argument");
  49.         }
  50.         Rational v = (Rational) value;
  51.         return new Rational(v.getNumerator(), v.getDenominator());
  52.     }
  53.  
  54.     @Override
  55.     public boolean isMutable() {
  56.         return true;
  57.     }
  58.  
  59.     //
  60.     // important: PGobject is postgresql-specific
  61.     //  
  62.     @Override
  63.     public Object nullSafeGet(ResultSet rs, String[] names, Object owners)
  64.             throws HibernateException, SQLException {
  65.         PGobject pgo = (PGobject) rs.getObject(names[0]);
  66.         if (rs.wasNull()) {
  67.             return null;
  68.         }
  69.         TheDbRationalType r = TheDbRationalType.parse(pgo.getValue(), "rational");
  70.         return new Rational(r.getNumerator(), r.getDenominator());
  71.     }
  72.  
  73.     //
  74.     // important: using Types.OTHER may be postgresql-specific
  75.     //  
  76.     @Override
  77.     public void nullSafeSet(PreparedStatement ps, Object value, int index)
  78.             throws HibernateException, SQLException {
  79.         if (value == null) {
  80.             ps.setNull(index, Types.OTHER);
  81.         } else if (!(value instanceof Rational)) {
  82.             throw new HibernateException("invalid argument");
  83.         } else {
  84.             Rational t = (Rational) value;
  85.             ps.setObject(index,
  86.                     new TheDbRationalType(t.getNumerator(), t.getDenominator()), Types.OTHER);
  87.         }
  88.     }
  89.  
  90.     @Override
  91.     public Object replace(Object original, Object target, Object owner)
  92.             throws HibernateException {
  93.         if (!(original instanceof Rational)
  94.                 || !(target instanceof Rational)) {
  95.             throw new HibernateException("invalid argument");
  96.         }
  97.         Rational r = (Rational) original;
  98.         return new Rational(r.getNumerator(), r.getDenominator());
  99.     }
  100.  
  101.     @Override
  102.     public Class returnedClass() {
  103.         return Rational.class;
  104.     }
  105.  
  106.     @Override
  107.     public int[] sqlTypes() {
  108.         return sqlTypesSupported;
  109.     }
  110.  
  111.     @Override
  112.     public String toString() {
  113.         String value = "";
  114.         if (denominator == 1) {
  115.             value = String.valueOf(numerator);
  116.         } else {
  117.             value = String.format("%d/%d", numerator, denominator);
  118.         }
  119.         return value;
  120.     }
  121.  
  122.     // for UserType
  123.     @Override
  124.     public int hashCode(Object value) {
  125.         Rational r = (Rational) value;
  126.         return (int) (31 * r.getNumerator() + r.getDenominator());
  127.     }
  128.    
  129.     @Override
  130.     public int hashCode() {
  131.         return hashCode(this);
  132.     }
  133.  
  134.     // for UserType
  135.     @Override
  136.     public boolean equals(Object left, Object right) {
  137.         if (left == right) {
  138.             return true;
  139.         }
  140.         if ((left == null) || (right == null)) {
  141.             return false;
  142.         }
  143.         if (!(left instanceof Rational) || !(right instanceof Rational)) {
  144.             return false;
  145.         }
  146.  
  147.         Rational l = (Rational) left;
  148.         Rational r = (Rational) right;
  149.         return (l.getNumerator() == r.getNumerator())
  150.                 && (l.getDenominator() == r.getDenominator());
  151.     }
  152.    
  153.     @Override
  154.     public boolean equals(Object value) {
  155.         return equals(this, value);
  156.     }
  157. }
public class Rational implements UserType, Serializable {
    private final int[] sqlTypesSupported = new int[] { Types.OTHER };
    private long numerator;
    private long denominator;

    public Rational() {
        numerator = 0;
        denominator = 1;
    }

    public Rational(long numerator, long denominator) {
        this.numerator = numerator;
        this.denominator = denominator;
    }

    public long getNumerator() {
        return numerator;
    }

    public long getDenominator() {
        return denominator;
    }

    @Override
    public Object assemble(Serializable cached, Object owner)
            throws HibernateException {
        if (!(cached instanceof Rational)) {
            throw new HibernateException("invalid argument");
        }
        Rational r = (Rational) cached;
        return new Rational(r.getNumerator(), r.getDenominator());
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        if (!(value instanceof Rational)) {
            throw new HibernateException("invalid argument");
        }
        return (Rational) value;
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        if (value == null) {
            return null
        }
        if (!(value instanceof Rational)) {
            throw new HibernateException("invalid argument");
        }
        Rational v = (Rational) value;
        return new Rational(v.getNumerator(), v.getDenominator());
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    //
    // important: PGobject is postgresql-specific
    //  
    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, Object owners)
            throws HibernateException, SQLException {
        PGobject pgo = (PGobject) rs.getObject(names[0]);
        if (rs.wasNull()) {
            return null;
        }
        TheDbRationalType r = TheDbRationalType.parse(pgo.getValue(), "rational");
        return new Rational(r.getNumerator(), r.getDenominator());
    }

    //
    // important: using Types.OTHER may be postgresql-specific
    //  
    @Override
    public void nullSafeSet(PreparedStatement ps, Object value, int index)
            throws HibernateException, SQLException {
        if (value == null) {
            ps.setNull(index, Types.OTHER);
        } else if (!(value instanceof Rational)) {
            throw new HibernateException("invalid argument");
        } else {
            Rational t = (Rational) value;
            ps.setObject(index,
                    new TheDbRationalType(t.getNumerator(), t.getDenominator()), Types.OTHER);
        }
    }

    @Override
    public Object replace(Object original, Object target, Object owner)
            throws HibernateException {
        if (!(original instanceof Rational)
                || !(target instanceof Rational)) {
            throw new HibernateException("invalid argument");
        }
        Rational r = (Rational) original;
        return new Rational(r.getNumerator(), r.getDenominator());
    }

    @Override
    public Class returnedClass() {
        return Rational.class;
    }

    @Override
    public int[] sqlTypes() {
        return sqlTypesSupported;
    }

    @Override
    public String toString() {
        String value = "";
        if (denominator == 1) {
            value = String.valueOf(numerator);
        } else {
            value = String.format("%d/%d", numerator, denominator);
        }
        return value;
    }

    // for UserType
    @Override
    public int hashCode(Object value) {
        Rational r = (Rational) value;
        return (int) (31 * r.getNumerator() + r.getDenominator());
    }
    
    @Override
    public int hashCode() {
        return hashCode(this);
    }

    // for UserType
    @Override
    public boolean equals(Object left, Object right) {
        if (left == right) {
            return true;
        }
        if ((left == null) || (right == null)) {
            return false;
        }
        if (!(left instanceof Rational) || !(right instanceof Rational)) {
            return false;
        }

        Rational l = (Rational) left;
        Rational r = (Rational) right;
        return (l.getNumerator() == r.getNumerator())
                && (l.getDenominator() == r.getDenominator());
    }
    
    @Override
    public boolean equals(Object value) {
        return equals(this, value);
    }
}

CustomTypes.hbm.xml

  1. <?xml version='1.0' encoding='utf-8'?>
  2. <!DOCTYPE hibernate-mapping PUBLIC
  3.        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  4.        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
  5.  
  6. <hibernate-mapping>
  7.  
  8.     <typedef name="javatest.rational" class="sandbox.RationalType"/>
  9.  
  10. </hibernate-mapping>
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

    <typedef name="javatest.rational" class="sandbox.RationalType"/>

</hibernate-mapping>

TestTable.hbm.xml

  1. <?xml version='1.0' encoding='utf-8'?>
  2. <!DOCTYPE hibernate-mapping PUBLIC
  3.        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
  4.        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
  5.  
  6. <hibernate-mapping>
  7.  
  8.     <class name="sandbox.TestTable" table="test_table">
  9.         <id name="id"/>
  10.         <property name="value" type="javatest.rational" />
  11.     </class>
  12.  
  13. </hibernate-mapping>
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

    <class name="sandbox.TestTable" table="test_table">
        <id name="id"/>
        <property name="value" type="javatest.rational" />
    </class>

</hibernate-mapping>

More Information

Creating a Scalar UDT in Java (user guide)

CREATE AGGREGATE documentation (PostgreSQL)

CREATE CAST documentation (PostgreSQL)

CREATE TYPE documentation (PostgreSQL)

CREATE OPERATOR documentation (PostgreSQL) (We’ll discuss this next time – it’s how you define tests for equality, etc.)

CREATE OPERATOR CLASS documentation (PostgreSQL) (We’ll discuss this next time – it’s how you define indexes.)

Interfacing user-defined types to indexes (PostgreSQL) (We’ll discuss this next time.)

Comments
No Comments »
Categories
java, pl/java, PostgreSQL, user-defined types
Comments rss Comments rss
Trackback Trackback