hawq-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dyozie <...@git.apache.org>
Subject [GitHub] incubator-hawq-docs pull request #83: HAWQ-1252 - pljava doc cleanup, workin...
Date Wed, 11 Jan 2017 22:32:25 GMT
Github user dyozie commented on a diff in the pull request:

    https://github.com/apache/incubator-hawq-docs/pull/83#discussion_r95687555
  
    --- 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:
     
    -- A savepoint must be rolled back or released in the function where it was set.
    -- A savepoint must not outlive the function where it was set.
    +- A savepoint must be rolled back or released in the function in which it was set.
    +- A savepoint must not outlive the function from which it was set.
     
     ## <a id="logging"></a>Logging 
     
    -PL/Java uses the standard Java Logger. Hence, you can write things like:
    +PL/Java uses the standard Java Logger. For example:
     
    -```java
    +``` java
     Logger.getAnonymousLogger().info( "Time is " + new 
    -Date(System.currentTimeMillis()));
    +    Date(System.currentTimeMillis()));
     ```
     
    -At present, the logger uses a handler that maps the current state of the HAWQ configuration
setting `log_min_messages` to a valid Logger level and that outputs all messages using the
HAWQ backend function `elog()`.
    +The logger uses a handler that maps the current setting of the HAWQ `log_min_messages`
server configuration parameter to a valid Java logging level, using the HAWQ backend function
`elog()` to output all messages.
     
    -**Note:** The `log_min_messages` setting is read from the database the first time a PL/Java
function in a session is executed. On the Java side, the setting does not change after the
first PL/Java function execution in a specific session until the HAWQ session that is working
with PL/Java is restarted.
    +**Note:** The `log_min_messages` setting is read from the database the first time a PL/Java
function in a session is executed and can only be changed by restarting the HAWQ session.
     
    -The following mapping apply between the Logger levels and the HAWQ backend levels.
    +Java to HAWQ log level mapping is identified in the table below.
    --- End diff --
    
    Edit for passive voice.


---
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 infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

Mime
View raw message