Jan 04

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

By thebearinboulderNo Comments

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
Related searches:
rational javatest return valueof public
gipoco.com is neither affiliated with the authors of this page nor responsible for its contents. This is a safe-cache copy of the original web site.