| <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™ 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> |
| <a href="#Deployer">Deployer</a><br> |
| <a href="#SQLJ_functions_">SQLJ functions</a><br> |
| <a href="#install_jar_">install_jar</a><br /> |
| <a href="#replace_jar_">replace_jar</a><br /> |
| <a href="#remove_jar_">remove_jar</a><br /> |
| <a href="#get_classpath_">get_classpath</a><br /> |
| <a href="#set_classpath_">set_classpath</a><br> |
| <a href="#Writing_Java_functions_">Writing Java functions</a><br> |
| <a href="#Type_mapping_">Type mapping</a><br> |
| <a href="#Returning_complex_types_">Returning complex types</a><br> |
| <a href="#Functions_returning_sets_">Functions returning sets</a><br> |
| <a href="#Using_JDBC_">Using JDBC</a><br> |
| <a href="#Exception_handling">Exception handling</a><br> |
| <a href="#Savepoints">Savepoints</a><br> |
| <a href="#Logging_">Logging</a><br> |
| <a href="#Security">Security</a><br> |
| <a href="#Installation">Installation</a><br> |
| <a href="#Trusted_language">Trusted language</a><br> |
| <a href="#Execution_of_the_deployment_descriptor">Execution of the deployment descriptor</a><br> |
| <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 <your classpath> org.postgresql.pljava.deploy.Deployer [ options ]</pre> |
| <p>It's recommended that create a shell script or a .bat script that does this for |
| you so that you don'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™ 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™ 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™ language and the sqlj procedures and loaded jars.</td> |
| </tr> |
| <tr> |
| <td width="160">-user <user name></td> |
| <td>Name of user that connects to the database. Default is the current user.</td> |
| </tr> |
| <tr> |
| <td width="160">-password <password></td> |
| <td>Password of user that connects to the database. Default is no password.</td> |
| </tr> |
| <tr> |
| <td width="160">-database <database></td> |
| <td>The name of the database to connect to. Default is to use the user name.</td> |
| </tr> |
| <tr> |
| <td width="160">-host <host name></td> |
| <td>Name of the host. Default is "localhost".</td> |
| </tr> |
| <tr> |
| <td width="160">-port <port number></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> </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: <deployment descriptor entry in the jar>SQLJDeploymentDescriptor: TRUE</pre> |
| <p>The deployment descriptor must have the following form:</p> |
| <pre><descriptor file> ::= |
| SQLActions <left bracket> <rightbracket> <equal sign> |
| { [ <double quote> <action group> <double quote> |
| [ <comma> <double quote> <action group> <double quote> ] ] } |
| <action group> ::= |
| <install actions> |
| | <remove actions> |
| <install actions> ::= |
| BEGIN INSTALL [ <command> <semicolon> ]... END INSTALL |
| <remove actions> ::= |
| BEGIN REMOVE [ <command> <semicolon> ]... END REMOVE |
| <command> ::= |
| <SQL statement> |
| | <implementor block> |
| <SQL statement> ::= !! <SQL token>... |
| <implementor block> ::= |
| BEGIN <implementor name> <SQL token>... END <implementor name> |
| <implementor name> ::= <identifier> |
| <SQL token> ::= !! an SQL lexical unit specified by the term "<token>" in Sub clause 5.2, "<token> and <separator>", 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[] = { |
| "BEGIN INSTALL |
| CREATE FUNCTION javatest.java_getTimestamp() |
| RETURNS timestamp |
| AS 'org.postgresql.pljava.example.Parameters.getTimestamp' |
| LANGUAGE java; |
| END INSTALL", |
| "BEGIN REMOVE |
| DROP FUNCTION javatest.java_getTimestamp(); |
| END REMOVE" |
| }</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(<jar_url>, <jar_name>, <deploy>);</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's an error if the jar is not found.</p> |
| <h4>Usage</h4> |
| <pre>SELECT sqlj.replace_jar(<jar_url>, <jar_name>, <redeploy>);</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's an error if the jar is |
| not found.</p> |
| <h4>Usage</h4> |
| <pre>SELECT sqlj.remove_jar(<jar_name>, <undeploy>);</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's an |
| error if the given schema does not exist.</p> |
| <h4>Usage</h4> |
| <pre>SELECT sqlj.get_classpath(<schema>);</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'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(<schema>, <classpath>);</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 "public" 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 'java.lang.System.getProperty' |
| LANGUAGE java;</pre> |
| <pre>SELECT getsysprop('user.home');</pre> |
| <h3><i><a name="Type_mapping_">Type mapping</a></i></h3> |
| <p>Scalar types are mapped in a straight forward way. Here'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>'char'</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 'foo.fee.Fum.trueIfEvenOrNull(java.lang.Integer)' |
| 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 'foo.fee.Fum.useComplexTest' |
| 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 "Base = \"" + base + |
| "\", incbase = \"" + incbase + |
| "\", ctime = \"" + ctime + "\""; |
| }</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 'foo.fee.Fum.createComplexTest' |
| 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'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'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> </p> |
| <h4>Returning a SETOF <scalar type></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's an example of a method that returns a SETOF varchar:</p> |
| <pre>CREATE FUNCTION javatest.getSystemProperties() |
| RETURNS SETOF varchar |
| AS 'foo.fee.Bar.getNames' |
| 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("Lisa"); |
| names.add("Bob"); |
| names.add("Bill"); |
| names.add("Sally"); |
| return names.iterator(); |
| } |
| }</pre> |
| <h4>Returning a SETOF <complex type></h4> |
| <p>A method returning a SETOF <complex type> 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 'foo.fee.Fum.listComplexTest' |
| 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 >= 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'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 'org.postgresql.pljava.example.Triggers.moddatetime' |
| LANGUAGE java"; |
| |
| 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, "can't process STATEMENT events"); |
| |
| if(td.isFiredAfter()) |
| throw new TriggerException(td, "must be fired before event"); |
| |
| if(!td.isFiredByUpdate()) |
| throw new TriggerException(td, "can only process UPDATE events"); |
| |
| ResultSet _new = td.getNew(); |
| String[] args = td.getArguments(); |
| if(args.length != 1) |
| throw new TriggerException(td, "one argument was expected"); |
| |
| _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("jdbc:default:connection"); </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'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( "Time is " + 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 "javaU". 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 "pljava". |
| Here's a sample postgresql.conf entry using all (3) of the variables currently |
| introduced:</p> |
| <p> </p> |
| <pre># define "pljava" 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> |