Invariant Properties

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>