docs/userguide.html

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<title>PL/Java 1.2 User Guide</title>
<style>
<!--
h2           { font-size: 18pt }
h3           { font-size: 14pt; margin-top: 12; margin-bottom: 3 }
h4           { font-size: 12pt; margin-top: 12; margin-bottom: 3 }
p            { margin-top: 0; margin-bottom: 6 }
pre          { margin-top: 6; margin-left: 15 }
-->
    </style>
</head>

<body>

<h1>PL/Java 1.2 User Guide</h1>
<p><font size="2">Java&#8482; is a registered trademark of Sun Microsystems, Inc. in the United States and other countries.</font></p>
<h2>Table of contents</h2>
<p><a href="#Utilities">Utilities</a><br>
&nbsp;&nbsp; <a href="#Deployer">Deployer</a><br>
&nbsp;<a href="#SQLJ_functions_">SQLJ functions</a><br>
&nbsp;&nbsp;&nbsp; <a href="#install_jar_">install_jar</a><br />
&nbsp;&nbsp;&nbsp; <a href="#replace_jar_">replace_jar</a><br />
&nbsp;&nbsp;&nbsp; <a href="#remove_jar_">remove_jar</a><br />
&nbsp;&nbsp;&nbsp; <a href="#get_classpath_">get_classpath</a><br />
&nbsp;&nbsp;&nbsp; <a href="#set_classpath_">set_classpath</a><br>
<a href="#Writing_Java_functions_">Writing Java functions</a><br>
&nbsp;&nbsp;&nbsp; <a href="#Type_mapping_">Type mapping</a><br>
&nbsp;&nbsp;&nbsp; <a href="#Returning_complex_types_">Returning complex types</a><br>
&nbsp;&nbsp;&nbsp; <a href="#Functions_returning_sets_">Functions returning sets</a><br>
&nbsp;&nbsp;&nbsp; <a href="#Using_JDBC_">Using JDBC</a><br>
&nbsp;&nbsp;&nbsp; <a href="#Exception_handling">Exception handling</a><br>
&nbsp;&nbsp;&nbsp; <a href="#Savepoints">Savepoints</a><br>
&nbsp;&nbsp;&nbsp; <a href="#Logging_">Logging</a><br>
<a href="#Security">Security</a><br>
&nbsp;&nbsp;&nbsp; <a href="#Installation">Installation</a><br>
&nbsp;&nbsp;&nbsp; <a href="#Trusted_language">Trusted language</a><br>
&nbsp;&nbsp;&nbsp; <a href="#Execution_of_the_deployment_descriptor">Execution of the deployment descriptor</a><br>
&nbsp;&nbsp;&nbsp; <a href="#Classpath_manipulation">Classpath manipulation</a><br>
<a href="#Module_Configuration">Module Configuration</a><br></p>
<h2><a name="Utilities">Utilities</a></h2>
<h3><i><a name="Deployer">Deployer</a></i></h3>
<p>When running the <code>deployer</code>, you must use a classpath that can see 
the <code>deploy.jar</code> found in the PL/Java distribution and the <code>postgresql.jar</code> 
from the PostgreSQL distribution. The former contains the code for the <code>deployer</code> 
command and the second includes the PostgreSQL JDBC driver. You then run the
<code>deployer</code> with the command:</p>
<pre>java -cp &lt;your classpath&gt; org.postgresql.pljava.deploy.Deployer [ options ]</pre>
<p>It&#39;s recommended that create a shell script or a .bat script that does this for 
you so that you don&#39;t have to do this over and over again.</p>
<h4>Deployer options</h4>
<table border="0" id="table1" style="margin-left: 8">
  <tr>
    <td width="160">-install</td>
    <td>Installs the Java&#8482; language along with the sqlj procedures. The deployer 
    will fail if the language is installed already.</td>
  </tr>
  <tr>
    <td width="160">-reinstall</td>
    <td>Reinstalls the Java&#8482; language and the sqlj procedures. This will effectively 
    drop all jar files that have been loaded.</td>
  </tr>
  <tr>
    <td width="160">-remove</td>
    <td>Drops the Java&#8482; language and the sqlj procedures and loaded jars.</td>
  </tr>
  <tr>
    <td width="160">-user &lt;user name&gt;</td>
    <td>Name of user that connects to the database. Default is the current user.</td>
  </tr>
  <tr>
    <td width="160">-password &lt;password&gt;</td>
    <td>Password of user that connects to the database. Default is no password.</td>
  </tr>
  <tr>
    <td width="160">-database &lt;database&gt;</td>
    <td>The name of the database to connect to. Default is to use the user name.</td>
  </tr>
  <tr>
    <td width="160">-host &lt;host name&gt;</td>
    <td>Name of the host. Default is &quot;localhost&quot;.</td>
  </tr>
  <tr>
    <td width="160">-port &lt;port number&gt;</td>
    <td>Port number. Default is blank.</td>
  </tr>
  <tr>
    <td width="160">-cygwin</td>
    <td>Use this option if the host runs on a Cygwin based windows platform. Affects 
    the name used for the PL/Java dynamic library.<p>NOTE This option should not 
    be used when running native the Win32 port.</p>
    </td>
  </tr>
</table>
<p>&nbsp;</p>
<h4>Deploying using SQL</h4>
<p>An alternative to using the deployer is to run the <code>install.sql</code> and 
<code>uninstall.sql</code> scripts that are included in the distribution.</p>
<h2><a name="SQLJ_functions_">SQLJ functions</a></h2>
<h3><i><a name="Deployment_descriptor_">Deployment descriptor</a></i></h3>
<p>The <code>install_jar</code>, <code>replace_jar</code>, and <code>remove_jar</code> 
can act on a deployment descriptor allowing SQL commands to be executed after the 
jar has been installed or prior to removal. The format of the deployment descriptor 
is stipulated by ISO/IEC 9075-13:2003.</p>
<p>The descriptor is added as a normal text file to your jar file. In the Manifest 
of the jar there must be an entry that appoints the file as the SQLJ deployment 
descriptor.</p>
<pre>Name: &lt;deployment descriptor entry in the jar&gt;SQLJDeploymentDescriptor: TRUE</pre>
<p>The deployment descriptor must have the following form:</p>
<pre>&lt;descriptor file&gt; ::=
SQLActions &lt;left bracket&gt; &lt;rightbracket&gt; &lt;equal sign&gt;
{ [ &lt;double quote&gt; &lt;action group&gt; &lt;double quote&gt;
  [ &lt;comma&gt; &lt;double quote&gt; &lt;action group&gt; &lt;double quote&gt; ] ] }
&lt;action group&gt; ::=
    &lt;install actions&gt;
  | &lt;remove actions&gt;
&lt;install actions&gt; ::=
  BEGIN INSTALL [ &lt;command&gt; &lt;semicolon&gt; ]... END INSTALL
&lt;remove actions&gt; ::=
  BEGIN REMOVE [ &lt;command&gt; &lt;semicolon&gt; ]... END REMOVE
&lt;command&gt; ::=
    &lt;SQL statement&gt;
  | &lt;implementor block&gt;
&lt;SQL statement&gt; ::= !! &lt;SQL token&gt;...
&lt;implementor block&gt; ::=
  BEGIN &lt;implementor name&gt; &lt;SQL token&gt;... END &lt;implementor name&gt;
&lt;implementor name&gt; ::= &lt;identifier&gt;
&lt;SQL token&gt; ::= !! an SQL lexical unit specified by the term &quot;&lt;token&gt;&quot; in Sub clause 5.2, &quot;&lt;token&gt; and &lt;separator&gt;&quot;, in ISO/IEC 9075-2.</pre>
<p>If implementor blocks are used, PL/Java will consider only those with implementor 
name PostgreSQL (case insensitive). Here is a small sample of a deployment descriptor:</p>
<pre>SQLActions[] = {
  &quot;BEGIN INSTALL
    CREATE FUNCTION javatest.java_getTimestamp()
      RETURNS timestamp
      AS &#39;org.postgresql.pljava.example.Parameters.getTimestamp&#39;
      LANGUAGE java;
      END INSTALL&quot;,
  &quot;BEGIN REMOVE
    DROP FUNCTION javatest.java_getTimestamp();
  END REMOVE&quot;
}</pre>
<h3><a name="install_jar_">install_jar</a></h3>
<p>The <code>install_jar</code> command loads a jarfile from a location appointed 
by an URL into the SQLJ jar repository. It is an error if a jar with the given name 
already exists in the repository.</p>
<h4>Usage</h4>
<pre>SELECT sqlj.install_jar(&lt;jar_url&gt;, &lt;jar_name&gt;, &lt;deploy&gt;);</pre>
<h4>Parameters</h4>
<table border="0" id="table2" width="100%">
  <tr>
    <td width="100"><b>jar_url</b> </td>
    <td>The URL that denotes the location of the jar that should be loaded.</td>
  </tr>
  <tr>
    <td width="100"><b>jar_name</b> </td>
    <td>This is the name by which this jar can be referenced once it has been loaded.</td>
  </tr>
  <tr>
    <td width="100"><b>deploy</b></td>
    <td>True if the jar should be deployed according to a deployment descriptor, 
    false otherwise.</td>
  </tr>
</table>
<h3><a name="replace_jar_">replace_jar</a></h3>
<p>The <code>replace_jar</code> will replace a loaded jar with another jar. Use 
this command to update already loaded files. It&#39;s an error if the jar is not found.</p>
<h4>Usage</h4>
<pre>SELECT sqlj.replace_jar(&lt;jar_url&gt;, &lt;jar_name&gt;, &lt;redeploy&gt;);</pre>
<h4>Parameters</h4>
<table border="0" width="100%" id="table3">
  <tr>
    <td width="100"><b>jar_url</b> </td>
    <td>The URL that denotes the location of the jar that should be loaded.</td>
  </tr>
  <tr>
    <td width="100"><b>jar_name</b></td>
    <td>The name of the jar to be replaced.</td>
  </tr>
  <tr>
    <td width="100"><b>redeploy</b></td>
    <td>True if the jar should be undeployed according to the deployment descriptor 
    of the old jar and deployed according to the deployment descriptor of the new 
    jar, false otherwise.</td>
  </tr>
</table>
<h3><a name="remove_jar_">remove_jar</a></h3>
<p>The <code>remove_jar</code> will drop the jar from the jar repository. Any classpath 
that references this jar will be updated accordingly. It&#39;s an error if the jar is 
not found.</p>
<h4>Usage</h4>
<pre>SELECT sqlj.remove_jar(&lt;jar_name&gt;, &lt;undeploy&gt;);</pre>
<h4>Parameters</h4>
<table border="0" width="100%" id="table4">
  <tr>
    <td width="100"><b>jar_name</b></td>
    <td>The name of the jar to be removed.</td>
  </tr>
  <tr>
    <td width="100"><b>undeploy</b></td>
    <td>True if the jar should be undeployed according to a deployment descriptor, 
    false otherwise.</td>
  </tr>
</table>
<h3><a name="get_classpath_">get_classpath</a></h3>
<p>The <code>get_classpath</code> will return the classpath that has been defined 
for the given schema or <code>NULL</code> if the schema has no classpath. It&#39;s an 
error if the given schema does not exist.</p>
<h4>Usage</h4>
<pre>SELECT sqlj.get_classpath(&lt;schema&gt;);</pre>
<h4>Parameters</h4>
<table border="0" width="100%" id="table5">
  <tr>
    <td width="100"><b>schema</b></td>
    <td>The name of the schema.</td>
  </tr>
</table>
<h3><a name="set_classpath_">set_classpath</a></h3>
<p>The <code>set_classpath</code> will define a classpath for the given schema. 
A classpath consists of a colon separated list of jar names. It&#39;s an error if the 
given schema does not exist or if one or more jar names references non existent 
jars.</p>
<h4>Usage</h4>
<pre>SELECT sqlj.set_classpath(&lt;schema&gt;, &lt;classpath&gt;);</pre>
<h4>Parameters</h4>
<table border="0" width="100%" id="table6">
  <tr>
    <td width="100"><b>schema</b></td>
    <td>The name of the schema.</td>
  </tr>
  <tr>
    <td width="100"><b>classpath</b></td>
    <td>The colon separated list of jar names.</td>
  </tr>
</table>
<h2><a name="Writing_Java_functions_">Writing Java functions</a></h2>
<h3><i><a name="SQL_declaration_">SQL declaration</a></i></h3>
<p>A Java function is declared with the name of a class and a static method on that 
class. The class will be resolved using the classpath that has been defined for 
the schema where the function is declared. If no classpath has been defined for 
that schema, the &quot;public&quot; schema is used. If no classpath is found there either, 
the class is resolved using the system classloader.</p>
<p>The following function can be declared to access the static method <code>getProperty</code> 
on <code>java.lang.System</code> class:</p>
<pre>CREATE FUNCTION getsysprop(VARCHAR)
  RETURNS VARCHAR
  AS &#39;java.lang.System.getProperty&#39;
  LANGUAGE java;</pre>
<pre>SELECT getsysprop(&#39;user.home&#39;);</pre>
<h3><i><a name="Type_mapping_">Type mapping</a></i></h3>
<p>Scalar types are mapped in a straight forward way. Here&#39;s a table of the current 
mappings (will be updated as more mappings are implemented).</p>
<table border="0" width="100%" id="table7" style="font-family: Courier New; font-size: 10pt; margin-left:15">
  <tr>
    <td>PostgreSQL</td>
    <td>Java</td>
  </tr>
  <tr>
    <td>bool</td>
    <td>boolean</td>
  </tr>
  <tr>
    <td>&#39;char&#39;</td>
    <td>byte</td>
  </tr>
  <tr>
    <td>int2</td>
    <td>short</td>
  </tr>
  <tr>
    <td>int4</td>
    <td>int</td>
  </tr>
  <tr>
    <td>int8</td>
    <td>long</td>
  </tr>
  <tr>
    <td>float4</td>
    <td>float</td>
  </tr>
  <tr>
    <td>float8</td>
    <td>double</td>
  </tr>
  <tr>
    <td>varchar</td>
    <td>java.lang.String</td>
  </tr>
  <tr>
    <td>text</td>
    <td>java.lang.String</td>
  </tr>
  <tr>
    <td>bytea</td>
    <td>byte[]</td>
  </tr>
  <tr>
    <td>date</td>
    <td>java.sql.Date</td>
  </tr>
  <tr>
    <td>time</td>
    <td>java.sql.Time (stored value treated as local time)</td>
  </tr>
  <tr>
    <td>timetz</td>
    <td>java.sql.Time</td>
  </tr>
  <tr>
    <td>timestamp</td>
    <td>java.sql.Timestamp (stored value treated as local time)</td>
  </tr>
  <tr>
    <td>timestamptz</td>
    <td>java.sql.Timestamp</td>
  </tr>
  <tr>
    <td>complex</td>
    <td>java.sql.ResultSet</td>
  </tr>
  <tr>
    <td>setof complex</td>
    <td>java.sql.ResultSet</td>
  </tr>
</table>
<p>All other types are currently mapped to <code>java.lang.String</code> and will 
utilize the standard <code>textin/textout</code> routines registered for respective 
type.</p>
<h4>NULL handling</h4>
<p>The scalar types that map to Java primitives can not be passed as <code>null</code> 
values. To enable this, those types can have an alternative mapping. You enable 
this mapping by explicitly denoting it in the method reference.</p>
<pre>CREATE FUNCTION trueIfEvenOrNull(integer)
  RETURNS bool
  AS &#39;foo.fee.Fum.trueIfEvenOrNull(java.lang.Integer)&#39;
  LANGUAGE java;</pre>
<p>In java, you would have something like:</p>
<pre>package foo.fee;
public class Fum
{
  static boolean trueIfEvenOrNull(Integer value)
  {
    return (value == null)
      ? true
      : (value.intValue() % 1) == 0;
  }
}</pre>
<p>The following two statements should both yield true:</p>
<pre>SELECT trueIfEvenOrNull(NULL);
SELECT trueIfEvenOrNull(4);</pre>
<p>In order to return <code>null</code> values from a Java method, you simply use 
the object type that corresponds to the primitive (i.e. you return <code>java.lang.Integer</code> 
instead of <code>int</code>). The PL/Java resolve mechanism will find the method 
regardless. Since Java cannot have different return types for methods with the same 
name, this does not introduce any ambiguity.</p>
<h4>Complex types</h4>
<p>A complex type will always be passed as a read-only <code>java.sql.ResultSet</code> 
with exaclty one row. The ResultSet will be positioned on its row so no call to 
next should be made. The values of the complex type are retrieved using the standard 
getter methods of the ResultSet.</p>
<p>Example:</p>
<pre>CREATE TYPE complexTest
  AS(base integer, incbase integer, ctime timestamptz);

CREATE FUNCTION useComplexTest(complexTest)
  RETURNS VARCHAR
  AS &#39;foo.fee.Fum.useComplexTest&#39;
  IMMUTABLE LANGUAGE java;</pre>
<p>In class Fum we add the static following static method:</p>
<pre>public static String useComplexTest(ResultSet complexTest)
throws SQLException
{
  int base = complexTest.getInt(1);
  int incbase = complexTest.getInt(2);
  Timestamp ctime = complexTest.getTimestamp(3);
  return &quot;Base = \&quot;&quot; + base +
    &quot;\&quot;, incbase = \&quot;&quot; + incbase +
    &quot;\&quot;, ctime = \&quot;&quot; + ctime + &quot;\&quot;&quot;;
}</pre>
<h3><i><a name="Returning_complex_types_">Returning complex types</a></i></h3>
<p>Java does not stipulate any way to create a ResultSet from scratch. Hence, returning 
a ResultSet is not an option. The SQL-2003 draft suggest that a complex return value 
instead is handled as an IN/OUT parameter and PL/Java implements it that way. If 
you declare a function that returns a complex type, you will need to use a Java 
method with boolean return type with a last parameter of type <code>java.sql.ResultSet</code>. 
The parameter will be initialized to an empty updateable ResultSet that contains 
exactly one row.</p>
<p>Assume that we still have the complexTest type created above.</p>
<pre>CREATE FUNCTION createComplexTest(int, int)
  RETURNS complexTest
  AS &#39;foo.fee.Fum.createComplexTest&#39;
  IMMUTABLE LANGUAGE java;</pre>
<p>The PL/Java method resolve will now find the following method in the Fum class:</p>
<pre>public static boolean complexReturn(int base, int increment, ResultSet receiver)
throws SQLException
{
  receiver.updateInt(1, base);
  receiver.updateInt(2, base + increment);
  receiver.updateTimestamp(3, new Timestamp(System.currentTimeMillis()));
  return true;
}</pre>
<p>The return value denotes if the receiver should be considered as a valid tuple 
(true) or NULL (false).</p>
<h3><i><a name="Functions_returning_sets_">Functions returning sets</a></i></h3>
<p>Returning sets is tricky. You don&#39;t want to first build a set and then return 
it since large sets would eat too much resources. Its far better to produce one 
row at a time. Incidentally, that&#39;s exactly what the PostgreSQL backend expects 
a function with SETOF return to do. You can return a SETOF a scalar type such as 
an int, float or varchar, or you can return a SETOF a complex type.</p>
<p>&nbsp;</p>
<h4>Returning a SETOF &lt;scalar type&gt;</h4>
<p>In order to return a set of a scalar type, you need create a Java method that 
returns something that implements the <code>java.util.Iterator</code> interface. 
Here&#39;s an example of a method that returns a SETOF varchar:</p>
<pre>CREATE FUNCTION javatest.getSystemProperties()
  RETURNS SETOF varchar
  AS &#39;foo.fee.Bar.getNames&#39;
  IMMUTABLE LANGUAGE java;</pre>
<p>The very rudimentary java method that returns an interator:</p>
<pre>package foo.fee;
import java.util.Iterator;

public class Bar
{
    public static Iterator getNames()
    {
        ArrayList names = new ArrayList();
        names.add(&quot;Lisa&quot;);
        names.add(&quot;Bob&quot;);
        names.add(&quot;Bill&quot;);
        names.add(&quot;Sally&quot;);
        return names.iterator();
    }
}</pre>
<h4>Returning a SETOF &lt;complex type&gt;</h4>
<p>A method returning a SETOF &lt;complex type&gt; must use either the interface <code>org.postgresql.pljava.ResultSetProvider 
or org.postgresql.pljava.ResultSetHandle</code>. 
The reason for having two interfaces is that they cater for optimal 
handling of two distinct use cases. The former is great when you want to 
dynamically create each row that is to be returned from the SETOF function. The 
latter makes sense when you want to return the result of an executed query.</p>
<h4>Using the ResultSetProvider interface</h4>
<p>This interface has two methods. The <code>boolean assignRowValues(java.sql.ResultSet tupleBuilder, int rowNumber)</code>
and the <code>void close()</code> method. The PostgreSQL query evaluator will call the <code>assignRowValues</code> repeatedly
until it returns <code>false</code> or until the evaluator decides that it does not need any more rows. It will then call
<code>close</code>.</p>
<p>You can use this interface the following way:</p>
<pre>CREATE FUNCTION javatest.listComplexTests(int, int)
  RETURNS SETOF complexTest
  AS &#39;foo.fee.Fum.listComplexTest&#39;
  IMMUTABLE LANGUAGE java;</pre>
<p>The function maps to a static java method that returns an instance that implements the ResultSetProvider interface.</p>
<pre>public class Fum implements ResultSetProvider
{
  private final int m_base;
  private final int m_increment;
  public Fum(int base, int increment)
  {
    m_base = base;
    m_increment = increment;
  }
  public boolean assignRowValues(ResultSet receiver, int currentRow)
  throws SQLException
  {
    // Stop when we reach 12 rows.
    //
    if(currentRow &gt;= 12)
      return false;
    receiver.updateInt(1, m_base);
    receiver.updateInt(2, m_base + m_increment * currentRow);
    receiver.updateTimestamp(3, new Timestamp(System.currentTimeMillis()));
    return true;
  }
  public void close()
  {
  	// Nothing needed in this example
  }
  public static ResultSetProvider listComplexTests(int base, int increment)
  throws SQLException
  {
    return new Fum(base, increment);
  }
}</pre>
<p>The <code>listComplextTests</code> method is called once. It may return <code>null</code> 
if no results are available or an instance of the <code>ResultSetProvider</code>. Here the <code>Fum</code> 
implements this interface so it returns an instance of itself. The method <code>assignRowValues</code> 
will then be called repeatedly until it returns false. At that time, <code>close</code> will be called</p>
<h4>Using the ResultSetHandle interface</h4>
<p>This interface is similar to the <code>ResultSetProvider</code> interface in that it has a 
<code>close()</code> method that will be called at the end. But instead of having the 
evaluator call a method that builds one row at a time, this method has a method 
that returns a <code>ResultSet</code>. The query evaluator will iterate over this set 
and deliver it's contents, one tuple at a time, to the caller until a call to 
<code>next()</code> returns <code>false</code> or the evaluator decides that no more
rows are needed.</p>
<p>Here is an example that executes a query using a statement that it obtained using the 
default connection. The SQL suitable for the deployment descriptor looks like this:</p>
<pre>CREATE FUNCTION javatest.listSupers()
  RETURNS SETOF pg_user
  AS 'org.postgresql.pljava.example.Users.listSupers'
  LANGUAGE java;

CREATE FUNCTION javatest.listNonSupers()
  RETURNS SETOF pg_user
  AS 'org.postgresql.pljava.example.Users.listNonSupers'
  LANGUAGE java;
</pre>
And in the Java package org.postgresql.pljava.example a class Users is added:
<pre>public class Users implements ResultSetHandle
{
  private final String m_filter;
  private Statement m_statement;

  public Users(String filter)
  {
    m_filter = filter;
  }

  public ResultSet getResultSet()
  throws SQLException
  {
    m_statement = DriverManager.getConnection("jdbc:default:connection").createStatement();
    return m_statement.executeQuery("SELECT * FROM pg_user WHERE " + m_filter);
  }

  public void close()
  throws SQLException
  {
    m_statement.close();
  }

  public static ResultSetHandle listSupers()
  {
    return new Users("usesuper = true");
  }

  public static ResultSetHandle listNonSupers()
  {
    return new Users("usesuper = false");
  }
}
</pre>

<h3><i><a name="Triggers_">Triggers</a></i></h3>
<p>The method signature of a trigger is predefined. A trigger method must always 
return void and have a <code>org.postgresql.pljava.TriggerData</code> parameter. 
No more, no less. The TriggerData interface provides access to two ResultSet instances; 
one representing the old row and one representing the new. The old row is read-only, 
the new row is updateable.</p>
<p>The sets are only available for triggers that are fired <code>ON EACH ROW</code>. 
Delete triggers have no new row, and insert triggers have no old row. Only update 
triggers have both.</p>
<p>In addition to the sets, several boolean methods exists to gain more information 
about the trigger. Here&#39;s an example trigger:</p>
<pre>CREATE TABLE mdt (
  id int4,
  idesc text,
  moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);

CREATE FUNCTION moddatetime()
  RETURNS trigger
  AS &#39;org.postgresql.pljava.example.Triggers.moddatetime&#39;
  LANGUAGE java&quot;;

CREATE TRIGGER mdt_moddatetime
  BEFORE UPDATE ON mdt
  FOR EACH ROW
  EXECUTE PROCEDURE moddatetime (moddate);</pre>
<p>The Java method in class org.postgresql.pljava.example.Triggers looks like this:</p>
<pre>/**
 * Update a modification time when the row is updated.
 */
static void moddatetime(TriggerData td)
throws SQLException
{
  if(td.isFiredForStatement())
    throw new TriggerException(td, &quot;can&#39;t process STATEMENT events&quot;);

  if(td.isFiredAfter())
    throw new TriggerException(td, &quot;must be fired before event&quot;);

  if(!td.isFiredByUpdate())
    throw new TriggerException(td, &quot;can only process UPDATE events&quot;);

  ResultSet _new = td.getNew();
  String[] args = td.getArguments();
  if(args.length != 1)
    throw new TriggerException(td, &quot;one argument was expected&quot;);

  _new.updateTimestamp(args[0], new Timestamp(System.currentTimeMillis()));
}</pre>
<h3><i><a name="Using_JDBC_">Using JDBC</a></i></h3>
<p>PL/Java contains a JDBC driver that maps to the PostgreSQL SPI functions. A connection 
that maps to the current transaction can be obtained using the following statement:</p>
<pre>Connection conn = DriverManager.getConnection(&quot;jdbc:default:connection&quot;); </pre>
<p>From there on, you can prepare and execute statements, just like with any other 
JDBC connection. There are a couple of limitations though:</p>
<ul type="square">
  <li>The transaction cannot be managed in any way. Thus, you cannot use methods 
  on the connection like:
  <ul type="disc">
    <li>commit()</li>
    <li>rollback()</li>
    <li>setAutoCommit()</li>
    <li>setTransactionIsolation()</li>
  </ul>
  </li>
  <li>Savepoints are available but only if you use PostgreSQL 8.0 or later and with 
  some restrictions. A savepoint cannot outlive the function in which it was set 
  and it must also be rolled back or released by that same function.</li>
  <li>ResultSet&#39;s returned from executeQuery() are always FETCH_FORWARD and CONCUR_READ_ONLY.</li>
  <li>Meta-data is only available in PL/Java 1.1 or higher.</li>
  <li>CallableStatement (for stored procedures) is not yet implemented.</li>
  <li>Clob/Blob types need more work. byte[] and String works fine for bytea/text 
  respectively. A more efficient mapping is planned where the actual array is not 
  copied.</li>
</ul>
<h3><i><a name="Exception_handling">Exception handling</a></i></h3>
<p>You can catch and handle an exception in the Postgres backend just like any 
other exceptoin. The <code>backend ErrorData</code> structure is 
exposed as a property in a class called <code>
org.postgresql.pljava.ServerException</code> (derived from
<code>java.sql.SQLException</code>) and the Java try/catch mechanism is 
synchronized with the backend mechanism.</p>
<h4>Important Note:</h4>
<p>You will not be able to continue executing backend functions until your 
function has returned and the error has been propagated when the backend has 
generated an exception unless you have used a savepoint. When a savepoint is 
rolled back, the exceptional condition is reset and you can continue your 
execution.</p>
<h3><i><a name="Savepoints">Savepoints</a></i></h3>
PostgreSQL savepoints are exposed using the <code>java.sql.Connection</code>
interface. Two restrictions apply.<ul>
<li>A savepoint must be rolled back or released in the function where it was 
set.</li>
<li>A savepoint must not outlive the function where it was set</li>
</ul>
<h3><i><a name="Logging_">Logging</a></i></h3>
<p>PL/Java uses the standard Java 1.4 Logger. Hence, you can write things like:</p>
<pre>Logger.getAnonymousLogger().info( &quot;Time is &quot; + new Date(System.currentTimeMillis()));</pre>
<p>At present, the logger is hardwired to a handler that maps the current state 
of the PostgreSQL configuration setting log_min_messages to a valid Logger level 
and that outputs all messages using the backend function elog(). The following 
mapping apply between the Logger levels and the PostgreSQL backend levels.</p>
<table border="0" width="43%" id="table8">
  <tr>
    <td><b>java.util.logging.Level</b></td>
    <td><b>PostgreSQL level</b></td>
  </tr>
  <tr>
    <td>SEVERE</td>
    <td>ERROR</td>
  </tr>
  <tr>
    <td>WARNING</td>
    <td>WARNING</td>
  </tr>
  <tr>
    <td>INFO</td>
    <td>INFO</td>
  </tr>
  <tr>
    <td>FINE</td>
    <td>DEBUG1</td>
  </tr>
  <tr>
    <td>FINER</td>
    <td>DEBUG2</td>
  </tr>
  <tr>
    <td>FINEST</td>
    <td>DEBUG3</td>
  </tr>
</table>
<h2><a name="Security">Security</a></h2>
<h3><i><a name="Installation">Installation</a></i></h3>
<p>Only a PostgreSQL super user can install PL/Java. The PL/Java utility functions 
are installed using <code>SECURITY DEFINER</code> so that they execute with the 
access permissions that where granted to the creator of the functions.</p>
<h3><i><a name="Trusted_language">Trusted language</a></i></h3>
<p>PL/Java is now a TRUSTED language. PostgreSQL stipulates that a language marked 
as trusted has no access to the filesystem and PL/Java enforces this. Any user can 
create and access functions or triggers in a trusted language. PL/Java also installs 
a language handler for the language &quot;javaU&quot;. This version is not trusted and only 
a superuser can create new functions that use it. Any user can still call the functions.</p>
<h3><i><a name="Execution_of_the_deployment_descriptor">Execution of the deployment 
descriptor</a></i></h3>
<p>The <code>install_jar</code>, <code>replace_jar</code>, and <code>remove_jar</code>, 
optionally executes commands found in a SQL deployment descriptor. Such commands 
are executed with the permissions of the caller. In other words, although the utility 
function is declared with <code>SECURITY DEFINER</code>, it switches back to the 
session user during execution of the deployment descriptor commands.</p>
<h3><i><a name="Classpath_manipulation">Classpath manipulation</a></i></h3>
<p>The function <code>set_classpath</code> requires the caller of the function has 
been granted <code>CREATE</code> permission on the affected schema.</p>

<h2><a name="Module_Configuration">Module Configuration</a>.</h2>
<p>PL/Java makes use of PostgreSQL <i>custom variable classes</i> in the
<code>postgresql.conf</code> configuration file to add some configuration parameters. 
PL/Java introduces a custom variable class named &quot;pljava&quot;. 
Here's a sample postgresql.conf entry using all (3) of the variables currently 
introduced:</p>
<p>&nbsp;</p>
<pre># define &quot;pljava&quot; as a custom variable class. This is a comma separated
# list of names.
#
custom_variable_classes = 'pljava'

# define the class path that the JVM will use when loading the
# initial library. Only meaningful for non GCJ installations
#
pljava.classpath = '/home/Tada/pljava/build/pljava.jar'

# Set the size of the prepared statement MRU cache
#
pljava.statement_cache_size = 10

# If true, lingering savepoints will be released on function exit. If false,
# the will be rolled back
#
pljava.release_lingering_savepoints = true

# Define startup options for the Java VM.
#
pljava.vmoptions = '-Xmx64M'

# Setting debug to true will cause the postgres process to go
# into a sleep(1) loop on its first call to java. This variable is
# only useful if you want to debug the PL/Java internal C code.
#
pljava.debug = false</pre>

</body>

</html>