[
https://issues.apache.org/jira/browse/HAWQ-1252?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15819363#comment-15819363
]
ASF GitHub Bot commented on HAWQ-1252:
--------------------------------------
Github user dyozie commented on a diff in the pull request:
https://github.com/apache/incubator-hawq-docs/pull/83#discussion_r95685816
--- 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.
--- End diff --
This sentence isn't complete. "... or an instance of the
`ResultSetProvider` interface <something>."
> PL/Java doc page - validate and enhance info
> --------------------------------------------
>
> Key: HAWQ-1252
> URL: https://issues.apache.org/jira/browse/HAWQ-1252
> Project: Apache HAWQ
> Issue Type: Improvement
> Components: Documentation
> Reporter: Lisa Owen
> Assignee: David Yozie
> Priority: Minor
>
> PL/Java docs section:
> - review and validate current content
> - make sure examples run and are relevant
> - develop new examples as appropriate
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
