Invariant Properties

  • rss
  • Home

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)

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

« Introduction to PostgreSQL PL/Java, part 4: User Defined Types ‘dropbox’ and noexec home directories »

Leave a Reply

Click here to cancel reply.

You must be logged in to post a comment.

Archives

  • May 2020 (1)
  • March 2019 (1)
  • August 2018 (1)
  • May 2018 (1)
  • February 2018 (1)
  • November 2017 (4)
  • January 2017 (3)
  • June 2016 (1)
  • May 2016 (1)
  • April 2016 (2)
  • March 2016 (1)
  • February 2016 (3)
  • January 2016 (6)
  • December 2015 (2)
  • November 2015 (3)
  • October 2015 (2)
  • August 2015 (4)
  • July 2015 (2)
  • June 2015 (2)
  • January 2015 (1)
  • December 2014 (6)
  • October 2014 (1)
  • September 2014 (2)
  • August 2014 (1)
  • July 2014 (1)
  • June 2014 (2)
  • May 2014 (2)
  • April 2014 (1)
  • March 2014 (1)
  • February 2014 (3)
  • January 2014 (6)
  • December 2013 (13)
  • November 2013 (6)
  • October 2013 (3)
  • September 2013 (2)
  • August 2013 (5)
  • June 2013 (1)
  • May 2013 (2)
  • March 2013 (1)
  • November 2012 (1)
  • October 2012 (3)
  • September 2012 (2)
  • May 2012 (6)
  • January 2012 (2)
  • December 2011 (12)
  • July 2011 (1)
  • June 2011 (2)
  • May 2011 (5)
  • April 2011 (6)
  • March 2011 (4)
  • February 2011 (3)
  • October 2010 (6)
  • September 2010 (8)

Recent Posts

  • 8-bit Breadboard Computer: Good Encapsulation!
  • Where are all the posts?
  • Better Ad Blocking Through Pi-Hole and Local Caching
  • The difference between APIs and SPIs
  • Hadoop: User Impersonation with Kerberos Authentication

Meta

  • Log in
  • Entries RSS
  • Comments RSS
  • WordPress.org

Pages

  • About Me
  • Notebook: Common XML Tasks
  • Notebook: Database/Webapp Security
  • Notebook: Development Tips

Syndication

Java Code Geeks

Know Your Rights

Support Bloggers' Rights
Demand Your dotRIGHTS

Security

  • Dark Reading
  • Krebs On Security Krebs On Security
  • Naked Security Naked Security
  • Schneier on Security Schneier on Security
  • TaoSecurity TaoSecurity

Politics

  • ACLU ACLU
  • EFF EFF

News

  • Ars technica Ars technica
  • Kevin Drum at Mother Jones Kevin Drum at Mother Jones
  • Raw Story Raw Story
  • Tech Dirt Tech Dirt
  • Vice Vice

Spam Blocked

53,793 spam blocked by Akismet
rss Comments rss valid xhtml 1.1 design by jide powered by Wordpress get firefox