Invariant Properties

    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());
    }

      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
      );

    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;
    }

      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 = <=
      );

    -- 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);

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.
     */
}

    /* 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);

    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();
    }

  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;

    // 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());
    }

    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
    );

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);
    }
}

<?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>

<?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>

public class AuditTrigger {

    public static void auditFoobar(TriggerData td) throws SQLException {

        Connection conn = DriverManager
                .getConnection("jdbc:default:connection");
        PreparedStatement ps = conn
                .prepareStatement("insert into javatest.foobar_audit(what, whenn, data) values (?, ?, ?::xml)");

        if (td.isFiredByInsert()) {
            ps.setString(1, "INSERT");
        } else if (td.isFiredByUpdate()) {
            ps.setString(1, "UPDATE");
        } else if (td.isFiredByDelete()) {
            ps.setString(1, "DELETE");
        }
        ps.setTimestamp(2, new Timestamp(System.currentTimeMillis()));

        ResultSet rs = td.getNew();
        if (rs != null) {
            ps.setString(3, toXml(rs));
        } else {
            ps.setNull(3, Types.VARCHAR);
        }

        ps.execute();
        ps.close();
    }

    // simple marshaler. We could use jaxb or similar library
    static String toXml(ResultSet rs) throws SQLException {
        String foo = rs.getString(1);
        if (rs.wasNull()) {
            foo = "";
        }
        String bar = rs.getString(2);
        if (rs.wasNull()) {
            bar = "";
        }
        return String.format("<my-class><foo>%s</foo><bar>%s</bar></my-class>", foo, bar);
    }
}

  CREATE TABLE javatest.foobar (
       foo   varchar(10),
       bar   varchar(10)
  );

  CREATE TABLE javatest.foobar_audit (
       what  varchar(10) not null,
       whenn timestamp not null,
       data  xml
  );

  CREATE FUNCTION javatest.audit_foobar()
      RETURNS trigger
      AS 'sandbox.AuditTrigger.auditFoobar'
      LANGUAGE 'java';

  CREATE TRIGGER foobar_audit
      AFTER INSERT OR UPDATE OR DELETE ON javatest.foobar
      FOR EACH ROW
      EXECUTE PROCEDURE javatest.audit_foobar();

/*
 * Copyright (c) 2004, 2005, 2006 TADA AB - Taby Sweden
 * Distributed under the terms shown in the file COPYRIGHT
 * found in the root folder of this project or at
 * http://eng.tada.se/osprojects/COPYRIGHT.html
 */
package org.postgresql.pljava;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * The SQL 2003 spec. does not stipulate a standard way of mapping
 * triggers to functions. The PLJava mapping use this interface. All
 * functions that are intended to be triggers must be public, static,
 * return void, and take a <code>TriggerData</code> as their argument.
 *
 * @author Thomas Hallgren
 */
public interface TriggerData
{
	/**
	 * Returns the ResultSet that represents the new row. This ResultSet will
	 * be null for delete triggers and for triggers that was fired for
	 * statement.
The returned set will be updateable and positioned on a
	 * valid row. When the trigger call returns, the trigger manager will see
	 * the changes that has been made to this row and construct a new tuple
	 * which will become the new or updated row.
	 *
	 * @return An updateable <code>ResultSet</code> containing one row or
	 *         <code>null</code>.
	 * @throws SQLException
	 *             if the contained native buffer has gone stale.
	 */
	ResultSet getNew() throws SQLException;

	/**
	 * Returns the ResultSet that represents the old row. This ResultSet will
	 * be null for insert triggers and for triggers that was fired for
	 * statement.
The returned set will be read-only and positioned on a
	 * valid row.
	 *
	 * @return A read-only <code>ResultSet</code> containing one row or
	 *         <code>null</code>.
	 * @throws SQLException
	 *             if the contained native buffer has gone stale.
	 */
	ResultSet getOld() throws SQLException;

	/**
	 * Returns the arguments for this trigger (as declared in the <code>CREATE TRIGGER</code>
	 * statement. If the trigger has no arguments, this method will return an
	 * array with size 0.
	 *
	 * @throws SQLException
	 *             if the contained native buffer has gone stale.
	 */
	String[] getArguments() throws SQLException;

	/**
	 * Returns the name of the trigger (as declared in the <code>CREATE TRIGGER</code>
	 * statement).
	 *
	 * @throws SQLException
	 *             if the contained native buffer has gone stale.
	 */
	String getName() throws SQLException;

	/**
	 * Returns the name of the table for which this trigger was created (as
	 * declared in the <code>CREATE TRIGGER</code statement). * * @throws SQLException * if the contained native buffer has gone stale. */ String getTableName() throws SQLException; /** * Returns the name of the schema of the table for which this trigger was created (as * declared in the <code>CREATE TRIGGER</code statement). * * @throws SQLException * if the contained native buffer has gone stale. */ String getSchemaName() throws SQLException; /** * Returns <code>true</code> if the trigger was fired after the statement * or row action that it is associated with. * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredAfter() throws SQLException; /** * Returns <code>true</code> if the trigger was fired before the * statement or row action that it is associated with. * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredBefore() throws SQLException; /** * Returns <code>true</code> if this trigger is fired once for each row * (as opposed to once for the entire statement). * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredForEachRow() throws SQLException; /** * Returns <code>true</code> if this trigger is fired once for the entire * statement (as opposed to once for each row). * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredForStatement() throws SQLException; /** * Returns <code>true</code> if this trigger was fired by a <code>DELETE</code>. * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredByDelete() throws SQLException; /** * Returns <code>true</code> if this trigger was fired by an <code>INSERT</code>. * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredByInsert() throws SQLException; /** * Returns <code>true</code> if this trigger was fired by an <code>UPDATE</code>. * * @throws SQLException * if the contained native buffer has gone stale. */ boolean isFiredByUpdate() throws SQLException; } </code></code>

/*
 * Copyright (c) 2004, 2005, 2006 TADA AB - Taby Sweden
 * Distributed under the terms shown in the file COPYRIGHT
 * found in the root folder of this project or at
 * http://eng.tada.se/osprojects/COPYRIGHT.html
 */
package org.postgresql.pljava;

import java.sql.SQLException;

/**
 * An exception specially suited to be thrown from within a method
 * designated to be a trigger function. The message generated by
 * this exception will contain information on what trigger and
 * what relation it was that caused the exception
 *
 * @author Thomas Hallgren
 */
public class TriggerException extends SQLException
{
    private static final long serialVersionUID = 5543711707414329116L;

    private static boolean s_recursionLock = false;

    public static final String TRIGGER_ACTION_EXCEPTION = "09000";

    private static final String makeMessage(TriggerData td, String message)
    {
        StringBuffer bld = new StringBuffer();
        bld.append("In Trigger ");
        if(!s_recursionLock)
        {
            s_recursionLock = true;
            try
            {
                bld.append(td.getName());
                bld.append(" on relation ");
                bld.append(td.getTableName());
            }
            catch(SQLException e)
            {
                bld.append("(exception while generating exception message)");
            }
            finally
            {
                s_recursionLock = false;
            }
        }
        if(message != null)
        {
            bld.append(": ");
            bld.append(message);
        }
        return bld.toString();
    }

    /**
     * Create an exception based on the <code>TriggerData</code> that was
     * passed to the trigger method.
     * @param td The <code>TriggerData</code> that was passed to the trigger
     * method.
     */
    public TriggerException(TriggerData td)
    {
        super(makeMessage(td, null), TRIGGER_ACTION_EXCEPTION);
    }

    /**
     * Create an exception based on the <code>TriggerData</code> that was
     * passed to the trigger method and an additional message.
     * @param td The <code>TriggerData</code> that was passed to the trigger
     * method.
     * @param reason An additional message with info about the exception.
     */
    public TriggerException(TriggerData td, String reason)
    {
        super(makeMessage(td, reason), TRIGGER_ACTION_EXCEPTION);
    }
}

    public static Iterator<String> colors() {
        List<String> colors = Arrays.asList("red", "green", "blue");
        return colors.iterator();
    }

  CREATE FUNCTION javatest.colors()
      RETURNS SETOF varchar
      AS 'sandbox.PLJava.colors'
      IMMUTABLE LANGUAGE java;

    public static Iterator seq(int start) {
        Iterator iter = null;
        try {
            iter = new SeqIterator(start);
        } catch (IllegalArgumentException e) {
            // should log error...
        }
        return iter;
    }

    public static class SeqIterator implements Iterator {
        private int next;
        private boolean done = false;

        public SeqIterator(int start) {
            if (start <= 0) {
                throw new IllegalArgumentException();
            }
            this.next = start;
        }

        @Override
        public boolean hasNext() {
            return !done;
        }

        @Override
        public Integer next() {
            int value = next;
            next = (next % 2 == 0) ? next / 2 : 3 * next + 1;
            done = (value == 1);
            return value;
        }

        @Override
        public void remove() {
            throw new UnsupportedOperationException();
        }
    }

  CREATE FUNCTION javatest.seq(int)
      RETURNS SETOF int
      AS 'sandbox.PLJava.seq'
      IMMUTABLE LANGUAGE java;

    public static boolean singleWord(ResultSet receiver) throws SQLException {
        receiver.updateString("English", "hello");
        receiver.updateString("Spanish", "hola");
        return true;
    }

  CREATE TYPE word AS (
      English varchar,
      Spanish varchar);

  CREATE FUNCTION javatest.single_word()
      RETURNS word
      AS 'sandbox.PLJava.singleWord'
      IMMUTABLE LANGUAGE java;

   public static ResultSetProvider listWords() {
        return new WordProvider();
    }

    public static class WordProvider implements ResultSetProvider {
        private final Map<String,String> words = new HashMap<String,String>();
        private final Iterator<String> keys;

        public WordProvider() {
            words.put("one", "uno");
            words.put("two", "dos");
            words.put("three", "tres");
            words.put("four", "quatro");
            keys = words.keySet().iterator();
        }

        @Override
        public boolean assignRowValues(ResultSet receiver, int currentRow)
                throws SQLException {
            if (!keys.hasNext()) {
                return false;
            }
            String key = keys.next();
            receiver.updateString("English", key);
            receiver.updateString("Spanish", words.get(key));
            return true;
        }

        @Override
        public void close() throws SQLException {
        }
    }

    CREATE FUNCTION javatest.list_words()
      RETURNS SETOF word
      AS 'sandbox.PLJava.listWords'
      IMMUTABLE LANGUAGE java;

    public static ResultSetHandle listUsers() {
        return new UsersHandle();
    }

    public static class UsersHandle implements ResultSetHandle {
        private Statement stmt;

        @Override
        public ResultSet getResultSet() throws SQLException {
            stmt = DriverManager.getConnection("jdbc:default:connection").createStatement();
            return stmt.executeQuery("SELECT * FROM pg_user");
        }

        @Override
        public void close() throws SQLException {
            stmt.close();
        }
    }

  CREATE FUNCTION javatest.list_users()
      RETURNS SETOF pg_user
      AS 'sandbox.PLJava.listUsers'
      LANGUAGE java;

/*
 * Copyright (c) 2004, 2005, 2006 TADA AB - Taby Sweden
 * Distributed under the terms shown in the file COPYRIGHT
 * found in the root folder of this project or at
 * http://eng.tada.se/osprojects/COPYRIGHT.html
 */
package org.postgresql.pljava;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * An implementation of this interface is returned from functions and procedures
 * that are declared to return <code>SET OF</code> a complex type. Functions that
 * return <code>SET OF</code> a simple type should simply return an
 * {@link java.util.Iterator Iterator}.
 * @author Thomas Hallgren
 */
public interface ResultSetProvider
{
	/**
	 * This method is called once for each row that should be returned from
	 * a procedure that returns a set of rows. The receiver
	 * is a {@link org.postgresql.pljava.jdbc.SingleRowWriter SingleRowWriter}
	 * writer instance that is used for capturing the data for the row.
	 * @param receiver Receiver of values for the given row.
	 * @param currentRow Row number. First call will have row number 0.
	 * @return <code>true</code> if a new row was provided, <code>false</code>
	 * if not (end of data).
	 * @throws SQLException
	 */
	boolean assignRowValues(ResultSet receiver, int currentRow)
	throws SQLException;

	/**
	 * Called after the last row has returned or when the query evaluator decides
	 * that it does not need any more rows.
	 */
	void close()
	throws SQLException;
}

/*
 * Copyright (c) 2004, 2005, 2006 TADA AB - Taby Sweden
 * Distributed under the terms shown in the file COPYRIGHT
 * found in the root directory of this distribution or at
 * http://eng.tada.se/osprojects/COPYRIGHT.html
 */
package org.postgresql.pljava;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * An implementation of this interface is returned from functions and procedures
 * that are declared to return <code>SET OF</code> a complex type in the form
 * of a {@link java.sql.ResultSet}. The primary motivation for this interface is
 * that an implementation that returns a ResultSet must be able to close the
 * connection and statement when no more rows are requested.
 * @author Thomas Hallgren
 */
public interface ResultSetHandle
{
	/**
	 * An implementation of this method will probably execute a query
	 * and return the result of that query.
	 * @return The ResultSet that represents the rows to be returned.
	 * @throws SQLException
	 */
	ResultSet getResultSet()
	throws SQLException;

	/**
	 * Called after the last row has returned or when the query evaluator decides
	 * that it does not need any more rows.
	 */
	void close()
	throws SQLException;
}

# apt-get install postgresql-9.1
# apt-get install postgresql-9.1-pljava-gcj

$ createdb template_java
$ psql -d template_java -c "update db_database set datistemplate='t' where datnam='template_java'"
$ psql -d template_java -f /usr/share/postgresql-9.1-pljava/install.sql

CREATE FUNCTION getsysprop(VARCHAR) RETURNS VARCHAR
  AS 'java.lang.System.getProperty'
  LANGUAGE java;

SELECT getsysprop('user.home');

package sandbox;

public class PLJava {
    public static String hello(String name) {
        if (name == null) {
            return null;
        }

        return "Hello, " + name + "!";
    }
}

--
-- load java library
--
-- parameters:
--   url_path - where the library is located
--   url_name - how the library is referred to later
--   deploy   - should the deployment descriptor be used?
--
select sqlj.install_jar('file:///tmp/sandbox.jar', 'sandbox', true);

--
-- set classpath to include new library.
--
-- parameters
--   schema    - schema (or database) name
--   classpath - colon-separated list of url_names.
--
select sqlj.set_classpath('mydb', 'sandbox');

-- -------------------
-- other procedures --
-- -------------------

--
-- reload java library
--
select sqlj.replace_jar('file:///tmp/sandbox.jar', 'sandbox', true);

--
-- remove java library
--
-- parameters:
--   url_name - how the library is referred to later
--   undeploy - should the deployment descriptor be used?
--
select sqlj.remove_jar('sandbox', true);

--
-- list classpath
--
select sqlj.get_classpath('mydb');

--

--
-- create function
--
CREATE FUNCTION mydb.hello(varchar) RETURNS varchar
  AS 'sandbox.PLJava.hello'
  LANGUAGE java;

--
-- drop this function
--
DROP FUNCTION mydb.hello(varchar);

--

SQLActions[] = {
  "BEGIN INSTALL
     CREATE FUNCTION javatest.hello(varchar)
       RETURNS varchar
       AS 'sandbox.PLJava.hello'
       LANGUAGE java;
   END INSTALL",
  "BEGIN REMOVE
     DROP FUNCTION javatest.hello(varchar);
   END REMOVE"
}

<plugin>
   <groupId>org.apache.maven.plugins</groupId>
   <artifactId>maven-jar-plugin</artifactId>
   <version>2.3.1</version>
   <configuration>
      <archive>
         <manifestSections>
            <manifestSection>
               <name>postgresql.ddr</name> <!-- filename -->
               <manifestEntries>
                  <SQLJDeploymentDescriptor>TRUE</SQLJDeploymentDescriptor>
               </manifestEntries>
            </manifestSection>
         </manifestSections>
      </archive>
   </configuration>
</plugin>