Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/83#discussion_r95687216
--- Diff: markdown/plext/using_pljava.html.md.erb ---
@@ -299,411 +331,770 @@ Scalar types are mapped in a straightforward way.
This table lists the current m
| complex | java.sql.ResultSet |
| setof complex | java.sql.ResultSet |
-All other types are mapped to `java.lang.String` and will utilize the
standard textin/textout routines registered for respective type.
+All other types are mapped to `java.lang.String` and will utilize the
standard textin/textout routines registered for the respective type.
### <a id="nullhandling"></a>NULL Handling
-The scalar types that map to Java primitives can not be passed as NULL
values. To pass NULL values, those types can have an alternative mapping. You
enable this mapping by explicitly denoting it in the method reference.
+The scalar types that map to Java primitives can not be passed as NULL
values to Java methods. To pass NULL values, those types should be mapped to
the Java object wrapper class that corresponds with the primitive, and must be
explicitly denoted in the method reference. For example, the object wrapper
class for the `integer` primitive type is `java.lang.Integer`.
-```sql
-=> CREATE FUNCTION trueIfEvenOrNull(integer)
- RETURNS bool
- AS 'foo.fee.Fum.trueIfEvenOrNull(java.lang.Integer)'
- LANGUAGE java;
-```
+Example: Handling Null Inputs
-The Java code would be similar to this:
-
-```java
-package foo.fee;
-public class Fum
-{
- static boolean trueIfEvenOrNull(Integer value)
- {
- return (value == null)
- ? true
- : (value.intValue() % 1) == 0;
- }
-}
-```
+1. Create a work area for the example:
-The following two statements both yield true:
+ ``` shell
+ $ mkdir pljava_work
+ $ cd pljava_work
+ $ export PLJAVAWORK=`pwd`
+ $ mkdir -p pljex/foo/fee
+ $ cd pljex/foo/fee
+ ```
-```sql
-=> SELECT trueIfEvenOrNull(NULL);
-=> SELECT trueIfEvenOrNull(4);
-```
+2. Create a new file named `Fum.java`, adding the following text to create
a class named `Fum` with a single method named `trueIfEvenOrNull()`. This
method takes an integer as input and returns true if the integer is even or
NULL, false otherwise:
-In order to return NULL values from a Java method, you use the object type
that corresponds to the primitive (for example, you return `java.lang.Integer`
instead of `int`). The PL/Java resolve mechanism finds the method regardless.
Since Java cannot have different return types for methods with the same name,
this does not introduce any ambiguity.
+ ``` shell
+ $ vi Fum.java
+ ```
+
+ ``` java
+ package foo.fee;
+ public class Fum
+ {
+ static boolean trueIfEvenOrNull(Integer value)
+ {
+ return (value == null)
+ ? true
+ : (value.intValue() % 1) == 0;
+ }
+ }
+ ```
+
+3. Compile the `Fum` class and create a JAR file for this class named
`pljex.jar`:
+
+ ``` shell
+ $ javac Fum.java
+ $ cd ../..
+ $ jar cf pljex.jar foo
+ ```
+
+4. Copy the JAR file to the default PL/Java classpath directory:
+
+ ``` shell
+ $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/
+ ```
+
+5. Start the `psql` subsystem:
+
+ ``` shell
+ $ psql -d testdb
+ ```
+
+6. Add the JAR file to the session-level classpath:
+
+ ``` sql
+ => SET pljava_classpath='pljex.jar';
+ ```
+
+7. Create a trusted PL/Java UDF that invokes the `Fum` class
`trueIfEvenOrNull()` method:
+
+ ``` sql
+ => CREATE FUNCTION isEvenOrNull(integer)
+ RETURNS bool
+ AS 'foo.fee.Fum.trueIfEvenOrNull(java.lang.Integer)'
+ LANGUAGE java;
+ ```
+
+ Notice that the UDF input type is `integer`, while the
`trueIfEvenOrNull()` Java method input is a `java.lang.Integer` object.
+
+8. Execute the UDF twice, once with a NULL and once with an even input:
+
+ ``` sql
+ => SELECT isEvenOrNull(NULL);
+ => SELECT isEvenOrNull(4);
+ ```
+
+ Both statements should return true.
### <a id="complextypes"></a>Complex Types
-A complex type will always be passed as a read-only `java.sql.ResultSet`
with exactly one row. The `ResultSet` is positioned on its row so a call to
`next()` should not be made. The values of the complex type are retrieved using
the standard getter methods of the `ResultSet`.
+PL/Java supports complex types. Use the `CREATE TYPE` SQL command to
create the complex type. Use the `CREATE FUNCTION` SQL command to define a
PL/Java UDF whose input argument is the new (complex) type.
-Example:
+A complex type is always passed to a Java method as a read-only
`java.sql.ResultSet` with exactly one row. The values of the specific fields in
the complex type are retrieved using the standard getter method associated with
the data type of each field present in the `ResultSet`.
-```sql
-=> CREATE TYPE complexTest
- AS(base integer, incbase integer, ctime timestamptz);
-=> CREATE FUNCTION useComplexTest(complexTest)
- RETURNS VARCHAR
- AS 'foo.fee.Fum.useComplexTest'
- IMMUTABLE LANGUAGE java;
-```
+Example: Complex Input Types
-In the Java class `Fum`, we add the following static method:
-
-```java
-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 + "\"";
-}
-```
+1. Add the following definitions and static method to the Java `Fum` class
you created in an earlier exercise. This method outputs the components of the
complex type comprised of two integer fields and a timestamp field:
+
+ ``` shell
+ $ cd $PLJAVAWORK/pljex/foo/fee
+ $ vi Fum.java
+ ```
+
+ ``` java
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Timestamp;
+ ```
+
+ ``` java
+ 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 + "\"";
+ }
+ ```
+
+ Add the `imports` under the `package` definition. And be sure to
include the `useComplexTypes()` method within the `Fum` class `{}`s.
+
+ Notice that the `ResultSet` object is immediately referenced; a call
to `next()` is not required.
+
+2. Compile the `Fum` class, create the JAR file, and copy the JAR file to
the default PL/Java classpath directory:
+
+ ``` shell
+ $ javac Fum.java
+ $ cd ../..
+ $ jar cf pljex.jar foo
+ $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/
+ ```
+
+5. Start the `psql` subsystem:
+
+ ``` shell
+ $ psql -d testdb
+ ```
+
+6. Add the JAR file to the session-level classpath:
+
+ ``` sql
+ => SET pljava_classpath='pljex.jar';
+ ```
+
+7. Create a custom complex type with 2 integers and a single timestamp
component:
+
+ ``` sql
+ => CREATE TYPE complexTestType
+ AS(base integer, incbase integer, ctime timestamptz);
+ ```
+
+7. Create a PL/Java UDF that invokes the `Fum` class `useComplexTest()`
method, specifying a `complexTestType` as input:
+
+ ```sql
+ => CREATE FUNCTION useComplexTest(complexTestType)
+ RETURNS VARCHAR
+ AS 'foo.fee.Fum.useComplexTest'
+ IMMUTABLE LANGUAGE java;
+ ```
+
+8. Execute the `useComplexTest()` UDF, providing 2 integers and a
timestamp as input:
+
+ ```sql
+ => SELECT useComplexTest( '(1,2,20170101010203)' );
+ ```
+
+ ```
+ usecomplextest
+ ------------------------------------------------------------
+ Base = "1", incbase = "2", ctime = "2017-01-01 01:02:03.0"
+ (1 row)
+ ```
+
+ Running the UDF displays the fields and values comprising the complex
type.
### <a id="returningcomplextypes"></a>Returning Complex Types
-Java does not stipulate any way to create a `ResultSet`. Hence, returning
a ResultSet is not an option. The SQL-2003 draft suggests that a complex return
value should be handled as an IN/OUT parameter. PL/Java implements a
`ResultSet` 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 `java.sql.ResultSet`. The parameter will be initialized to an
empty updateable ResultSet that contains exactly one row.
+As Java does not define any way to create a `ResultSet`, returning a
`ResultSet` from a Java method is not an option. The SQL-2003 draft suggests
that a complex return value should be handled as an input/output argument.
Conveniently, PL/Java implements a `ResultSet` that way. To declare a function
that returns a complex type, you must implement a Java method with a `boolean`
return type and a last input argument of type `java.sql.ResultSet`. This
input/output argument will be initialized to an empty updateable `ResultSet`
that contains exactly one row.
-Assume that the complexTest type in previous section has been created.
+Example: Complex Return Types
-```sql
-=> CREATE FUNCTION createComplexTest(int, int)
- RETURNS complexTest
- AS 'foo.fee.Fum.createComplexTest'
- IMMUTABLE LANGUAGE java;
-```
+1. Create the `complexTestType` type definition if you did not yet create
it:
-The PL/Java method resolve will now find the following method in the `Fum`
class:
-
-```java
-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;
-}
-```
+ ``` sql
+ => CREATE TYPE complexTestType
+ AS(base integer, incbase integer, ctime timestamptz);
+ ```
+
+2. Add the following static method to the Java class `Fum` you created in
an earlier exercise. This method takes two integers as input, returning a
complex type consisting of the first input integer, first input integer added
to the second input integer, and a current timestamp:
+
+ ``` shell
+ $ cd $PLJAVAWORK/pljex/foo/fee
+ $ vi Fum.java
+ ```
+
+ ``` java
+ 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;
+ }
+ ```
+
+ The return value denotes if the `receiver` should be considered as a
valid tuple (true) or NULL (false).
+
+3. Compile the `Fum` class, create the JAR file, and copy the JAR file to
the default PL/Java classpath directory:
+
+ ``` shell
+ $ javac Fum.java
+ $ cd ../..
+ $ jar cf pljex.jar foo
+ $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/
+ ```
+
+5. Start the `psql` subsystem:
+
+ ``` shell
+ $ psql -d testdb
+ ```
+
+6. Add the JAR file to the session-level classpath:
-The return value denotes if the receiver should be considered as a valid
tuple (true) or NULL (false).
+ ``` sql
+ => SET pljava_classpath='pljex.jar';
+ ```
+
+7. Create a PL/Java UDF that invokes the `Fum` class `createComplexType()`
method, taking two integers as input:
+
+ ```sql
+ => CREATE FUNCTION createComplexTest(int, int)
+ RETURNS complexTestType
+ AS 'foo.fee.Fum.complexReturn'
+ IMMUTABLE LANGUAGE java;
+ ```
+
+8. Execute the `createComplexTest()` UDF, providing 2 integers as input:
+
+ ```sql
+ => SELECT createComplexTest(11,22);
+ ```
+
+ ```
+ createcomplextest
+ --------------------------------------
+ (11,33,"2016-12-31 23:04:09.388-08")
+ (1 row)
+ ```
+
+ As described, the UDF, when executed, returns the first input integer,
the sum of the first and second input integers, and the current timestamp.
+
+### <a id="functionreturnsets"></a>Functions that Return Sets
-### <a id="functionreturnsets"></a>Functions that Return Sets
+PL/Java supports user-defined functions that return sets of both scalar
and complex types.
-When returning result set, you should not build a result set before
returning it, because building a large result set would consume a large amount
of resources. It is better to produce one row at a time. Incidentally, that is
what the HAWQ 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.
+HAWQ backend a function that `RETURNS` a `SETOF` to return one row at a
time.
### <a id="returnsetofscalar"></a>Returning a SETOF \<scalar type\>
-In order to return a set of a scalar type, you need create a Java method
that returns something that implements the `java.util.Iterator` interface. Here
is an example of a method that returns a SETOF varchar:
+To return a set of a scalar type, the Java method must return an object
that implements the `java.util.Iterator` interface.
-```sql
-=> CREATE FUNCTION javatest.getSystemProperties()
- RETURNS SETOF varchar
- AS 'foo.fee.Bar.getNames'
- IMMUTABLE LANGUAGE java;
-```
+Example: Function that returns a SETOF varchar:
-This simple Java method returns an iterator:
+1. Create a new file named `Bar.java`, adding the following text to create
a class named `Bar` with a single method named `getNames()`. This method uses
an `Iterator` to collect and return a list of string names:
-```java
-package foo.fee;
-import java.util.Iterator;
+ ``` shell
+ $ cd $PLJAVAWORK/pljex/foo/fee
+ $ vi Bar.java
+ ```
-public class Bar
-{
- public static Iterator getNames()
+ ``` java
+ package foo.fee;
+ import java.util.ArrayList;
+ import java.util.Iterator;
+
+ public class Bar
{
- ArrayList names = new ArrayList();
- names.add("Lisa");
- names.add("Bob");
- names.add("Bill");
- names.add("Sally");
- return names.iterator();
+ public static Iterator getNames()
+ {
+ ArrayList names = new ArrayList();
+ names.add("Lisa");
+ names.add("Bob");
+ names.add("Bill");
+ names.add("Sally");
+ return names.iterator();
+ }
}
-}
-```
+ ```
+
+
+3. Compile the `Bar` class (ignore warnings), create the JAR file, and
copy the JAR file to the default PL/Java classpath directory:
+
+ ``` shell
+ $ javac *.java
+ $ cd ../..
+ $ jar cf pljex.jar foo
+ $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/
+ ```
+
+5. Start the `psql` subsystem:
+
+ ``` shell
+ $ psql -d testdb
+ ```
+
+6. Add the JAR file to the session-level classpath:
+
+ ``` sql
+ => SET pljava_classpath='pljex.jar';
+ ```
+
+7. Create a PL/Java UDF that invokes the `Bar` class `getNames()` method:
+
+ ``` sql
+ => CREATE FUNCTION getListOfNames()
+ RETURNS SETOF varchar
+ AS 'foo.fee.Bar.getNames'
+ IMMUTABLE LANGUAGE java;
+ ```
+
+8. Execute the UDF:
+
+ ``` sql
+ => SELECT getListOfNames();
+ ```
+
+ ```
+ getlistofnames
+ ----------------
+ Lisa
+ Bob
+ Bill
+ Sally
+ (4 rows)
+ ```
+
+ The UDF returns a list of four string names.
### <a id="returnsetofcomplex"></a>Returning a SETOF \<complex type\>
-A method returning a SETOF <complex type> must use either the interface
`org.postgresql.pljava.ResultSetProvider` or
`org.postgresql.pljava.ResultSetHandle`. The reason for having two interfaces
is that they cater for optimal handling of two distinct use cases. The former
is for cases when you want to dynamically create each row that is to be
returned from the SETOF function. The latter makes is in cases where you want
to return the result of an executed query.
+A method returning a set of \<complex type\> must implement either the
`org.postgresql.pljava.ResultSetProvider` interface or the
`org.postgresql.pljava.ResultSetHandle` interface. The interfaces provide
optimal handling for distinct use cases. Use
`org.postgresql.pljava.ResultSetProvider` when you want to dynamically create
each row the function returns. Use `org.postgresql.pljava.ResultSetHandle` in
cases where you want to return the result of an executed query.
#### Using the ResultSetProvider Interface
-This interface has two methods. The boolean
`assignRowValues(java.sql.ResultSet tupleBuilder, int rowNumber)` and the `void
close()` method. The HAWQ query evaluator will call the `assignRowValues`
repeatedly until it returns false or until the evaluator decides that it does
not need any more rows. Then it calls close.
+The `ResultSetProvider` interface has two methods:
-You can use this interface the following way:
+- `boolean assignRowValues(java.sql.ResultSet tupleBuilder, int rowNumber)`
+- `void close()`
-```sql
-=> CREATE FUNCTION javatest.listComplexTests(int, int)
- RETURNS SETOF complexTest
- AS 'foo.fee.Fum.listComplexTest'
- IMMUTABLE LANGUAGE java;
-```
+The HAWQ query evaluator calls `assignRowValues()` repeatedly until it
returns false or until the evaluator decides that it does not need any more
rows. At that point, it will call `close()`.
+
+Example: Using the `ResultSetProvider` Interface
+
+1. Create a new file named `FumSetOfComplex.java`, adding the following
text to create a class named `FumSetOfComplex` that implements the
`ResultSetProvider` interface:
+
+ ``` shell
+ $ cd $PLJAVAWORK/pljex/foo/fee
+ $ vi FumSetOfComplex.java
+ ```
+
+ ``` java
+ package foo.fee;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Timestamp;
+ import org.postgresql.pljava.ResultSetProvider;
-The function maps to a static java method that returns an instance that
implements the `ResultSetProvider` interface.
-
-```java
-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
+ public class FumSetOfComplex implements ResultSetProvider
+ {
+ private final int m_base;
+ private final int m_increment;
+ public FumSetOfComplex(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
+ throws SQLException
+ {
+ 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,
+ return true;
+ }
+ public void close()
+ {
+ /* Nothing needed in this example */
+ }
+ public static ResultSetProvider listComplex(int base,
int increment)
- throws SQLException
- {
- return new Fum(base, increment);
- }
-}
-```
+ throws SQLException
+ {
+ return new FumSetOfComplex(base, increment);
+ }
+ }
+ ```
+
+ The `listComplex()` method is called once. It will return NULL if no
results are available or an instance of the `ResultSetProvider` interface. The
Java class `FumSetOfComplex` implements this interface to return an instance of
itself. `assignRowValues()` is called repeatedly until it returns false. At
that time, `close()` is called.
+
+3. Compile the `FumSetOfComplex` class, create the JAR file, and copy the
JAR file to the default PL/Java classpath directory:
+
+ ``` shell
+ $ javac -classpath /usr/local/hawq/lib/postgresql/pljava.jar
FumSetOfComplex.java
+ $ cd ../..
+ $ jar cf pljex.jar foo
+ $ cp pljex.jar /usr/local/hawq/lib/postgresql/java/
+ ```
+
+5. Start the `psql` subsystem:
+
+ ``` shell
+ $ psql -d testdb
+ ```
+
+6. Add the JAR file to the session-level classpath:
+
+ ``` sql
+ => SET pljava_classpath='pljex.jar';
+ ```
+
+7. Create a PL/Java UDF that invokes the `FumSetOfComplex` class
`listComplexTest()` method:
+
+ ```sql
+ => CREATE FUNCTION listComplexTest(int, int)
+ RETURNS SETOF complexTestType
+ AS 'foo.fee.FumSetOfComplex.listComplex'
+ IMMUTABLE LANGUAGE java;
+ ```
+
+8. Execute the UDF:
+
+ ``` sql
+ => SELECT listComplexTest(1,2);
+ ```
+
+ ```
+ testdb=# SELECT listComplexTest(1,2);
+ listcomplextest
+ -------------------------------------
+ (1,1,"2017-01-01 01:20:32.888-08")
+ (1,3,"2017-01-01 01:20:32.888-08")
+ (1,5,"2017-01-01 01:20:32.888-08")
+ (1,7,"2017-01-01 01:20:32.888-08")
+ (1,9,"2017-01-01 01:20:32.888-08")
+ ...
+ (12 rows)
+ ```
-The `listComplextTests` method is called once. It may return NULL if no
results are available or an instance of the `ResultSetProvider`. Here the Java
class `Fum` implements this interface so it returns an instance of itself. The
method `assignRowValues` will then be called repeatedly until it returns false.
At that time, close will be called.
#### Using the ResultSetHandle Interface
-This interface is similar to the `ResultSetProvider` interface in that it
has a `close()` 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 `ResultSet`. The query evaluator will iterate over this
set and deliver the `ResultSet` contents, one tuple at a time, to the caller
until a call to `next()` returns false or the evaluator decides that no more
rows are needed.
+Classes implementing the `ResultSetHandle` interface will include a method
to return a `ResultSet` named `getResultSet()`. The query evaluator will
iterate over this set and deliver the `ResultSet` contents, one tuple at a
time, to the caller until a call to `next()` returns false or the evaluator
decides that no more rows are needed.
-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:
+Example: Using the `ResultSetHandle` Interface
-```sql
-=> 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;
-```
+1. A `Users` class is defined in the Java example package
`org.postgresql.example` (refer to
[`example`](https://github.com/apache/incubator-hawq/blob/master/src/pl/pljava/src/java/examples/org/postgresql/example)
for example source files):
-And in the Java package `org.postgresql.pljava.example` a class `Users` is
added:
-
-```java
-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").cr
-eateStatement();
- 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");
- }
-}
-```
+ ``` java
+ package org.postgresql.example;
+
+ import java.sql.DriverManager;
+ import java.sql.ResultSet;
+ import java.sql.SQLException;
+ import java.sql.Statement;
+
+ import org.postgresql.pljava.ResultSetHandle;
+
+ 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");
+ }
+ }
+ ```
+
+ The `listSupers()` and `listNonSupers()` methods each execute a query
to return a `ResultSetHandle` containing a list of those users with and without
superuser privileges, respectively.
+
+2. Copy the `examples.jar` file to the default PL/Java classpath directory:
+
+ ``` shell
+ $ cp /usr/local/hawq/share/postgresql/pljava/examples.jar
/usr/local/hawq/lib/postgresql/java/
+ ```
+
+2. Start the `psql` subsystem:
+
+ ``` shell
+ $ psql -d testdb
+ ```
+
+6. Add the `examples.jar` JAR file to the session-level classpath:
+
+ ``` sql
+ => SET pljava_classpath='examples.jar';
+ ```
+
+7. Create PL/Java UDFs that execute the `listSupers()` and
`listNonSupers()` methods in the `org.postgresql.example.Users` class:
+
+ ``` sql
+ => CREATE FUNCTION plistSupers()
+ RETURNS SETOF pg_user
+ AS 'org.postgresql.example.Users.listSupers'
+ LANGUAGE java;
+ => CREATE FUNCTION plistNonSupers()
+ RETURNS SETOF pg_user
+ AS 'org.postgresql.example.Users.listNonSupers'
+ LANGUAGE java;
+ ```
+
+8. Execute the UDFs:
+
+ ``` sql
+ => SELECT plistSupers();
+ ```
+
+ ```
+ plistsupers
+ -------------------------------
+ (gpadmin,10,t,t,t,********,,)
+ (1 row)
+ ```
+
+ ``` sql
+ => SELECT plistNonSupers();
+ ```
+
+ ```
+ plistnonsupers
+ ---------------
+ (0 rows)
+ ```
+
+
## <a id="usingjdbc"></a>Using JDBC
-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:
+PL/Java includes a JDBC driver. This driver invokes HAWQ internal SPI
routines. The driver is essential; it is common for functions to make calls
back to the database to fetch data. When PL/Java user-defined functions fetch
data, they must use the same transactional boundaries that are used by the main
function that entered the PL/Java execution context.
-```java
+You can obtain a PL/Java JDBC driver connection mapping to the current
transaction with the following Java statement:
+
+``` java
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
```
-After obtaining a connection, you can prepare and execute statements
similar to other JDBC connections. These are limitations for the PL/Java JDBC
driver:
+After obtaining a connection, you can prepare and execute statements
similar to other JDBC connections. Refer to the `Users` class Java source code
from the example above.
+
+Limitations of the PL/Java JDBC driver include the following:
-- The transaction cannot be managed in any way. Thus, you cannot use
methods on the connection such as:
+- A transaction cannot be managed in any way. As such, you cannot use the
following methods on the JDBC connection:
- `commit()`
- `rollback()`
- `setAutoCommit()`
- `setTransactionIsolation()`
-- Savepoints are available with some restrictions. A savepoint cannot
outlive the function in which it was set and it must be rolled back or released
by that same function.
-- A ResultSet returned from `executeQuery()` are always `FETCH_FORWARD`
and `CONCUR_READ_ONLY`.
-- Meta-data is only available in PL/Java 1.1 or higher.
+- Savepoints are available, with some restrictions. A savepoint cannot
outlive the function in which it was set, and it must be rolled back or
released by that same function.
+- A `ResultSet` returned from `executeQuery()` is always `FETCH_FORWARD`
and `CONCUR_READ_ONLY`.
- `CallableStatement` (for stored procedures) is not implemented.
-- The types `Clob` or `Blob` are not completely implemented, they need
more work. The types `byte[]` and `String` can be used for `bytea` and `text`
respectively.
+- The types `Clob` or `Blob` are not completely implemented. Use the types
`byte[]` and `String` for `bytea` and `text`, respectively.
## <a id="exceptionhandling"></a>Exception Handling
-You can catch and handle an exception in the HAWQ backend just like any
other exception. The backend `ErrorData` structure is exposed as a property in
a class called `org.postgresql.pljava.ServerException` (derived from
`java.sql.SQLException`) and the Java try/catch mechanism is synchronized with
the backend mechanism.
+You can catch and handle an exception in the HAWQ backend just like any
other exception. The backend `ErrorData` structure is exposed as a property in
the `org.postgresql.pljava.internal.ServerException` class (derived from
`java.sql.SQLException`), and the Java `try/catch` construct is synchronized
with the backend mechanism.
-**Important:** 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.
+**Important:** If the backend has generated an exception and you have set
a savepoint, the exception condition is reset when the savepoint is rolled
back, allowing you to continue your execution. If the backend has generated an
exception and you have *not* used a savepoint, you will not be able to continue
executing backend functions until your function has returned and the error has
been propagated.
## <a id="savepoints"></a>Savepoints
-HAWQ savepoints are exposed using the `java.sql.Connection` interface. Two
restrictions apply.
+HAWQ savepoints are exposed using the `java.sql.Connection` interface. Two
restrictions apply:
--- End diff --
"savepoints are exposed" -> "exposes savepoints"
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---
