Package org.postgresql.pljava.example.annotation

Class TypeRoundTripper

java.lang.Object

org.postgresql.pljava.example.annotation.TypeRoundTripper

@SQLAction(requires={"TypeRoundTripper.roundTrip","point mirror type"},
           install={" SELECT  CASE WHEN every(orig = roundtripped)  THEN javatest.logmessage(\'INFO\', \'timestamp roundtrip passes\')  ELSE javatest.logmessage(\'WARNING\', \'timestamp roundtrip fails\')  END FROM  (VALUES   (timestamp \'2017-08-21 18:25:29.900005\'),   (timestamp \'1970-03-07 17:37:49.300009\'),   (timestamp \'1919-05-29 13:08:33.600001\')  ) AS p(orig),  roundtrip(p) AS (roundtripped timestamp)"," SELECT  CASE WHEN every(orig = roundtripped)  THEN javatest.logmessage(\'INFO\', \'timestamptz roundtrip passes\')  ELSE javatest.logmessage(\'WARNING\', \'timestamptz roundtrip fails\')  END FROM  (VALUES   (timestamptz \'2017-08-21 18:25:29.900005Z\'),   (timestamptz \'1970-03-07 17:37:49.300009Z\'),   (timestamptz \'1919-05-29 13:08:33.600001Z\')  ) AS p(orig),  roundtrip(p) AS (roundtripped timestamptz)"," SELECT  CASE WHEN classjdbc = \'org.postgresql.pljava.example.annotation.Point\'  THEN javatest.logmessage(\'INFO\', \'issue192 test passes\')  ELSE javatest.logmessage(\'WARNING\', \'issue192 test fails\')  END FROM  (VALUES (point \'0,0\')) AS p,  roundtrip(p) AS (classjdbc text)"," SELECT  CASE WHEN every(outcome.ok)  THEN javatest.logmessage(\'INFO\',    \'boolean[] passes\')  ELSE javatest.logmessage(\'WARNING\', \'boolean[] fails\')  END FROM  (SELECT \'{t,null,f}\'::boolean[]) AS p(orig),  (VALUES (\'\'), (\'[Ljava.lang.Boolean;\'), (\'[Z\')) as q(rqcls),  roundtrip(p, rqcls) AS (class text, roundtripped boolean[]),  LATERAL (SELECT   (rqcls = class OR rqcls = \'\')   AND roundtripped =   CASE WHEN class LIKE \'[_\' THEN array_replace(orig, null, false)   ELSE orig END  ) AS outcome(ok)"," SELECT  CASE WHEN every(outcome.ok)  THEN javatest.logmessage(\'INFO\',    \'\"char\"[] passes\')  ELSE javatest.logmessage(\'WARNING\', \'\"char\"[] fails\')  END FROM  (SELECT \'{A,null,B}\'::\"char\"[]) AS p(orig),  (VALUES (\'\'), (\'[Ljava.lang.Byte;\'), (\'[B\')) as q(rqcls),  roundtrip(p, rqcls) AS (class text, roundtripped \"char\"[]),  LATERAL (SELECT   (rqcls = class OR rqcls = \'\')   AND roundtripped =   CASE WHEN class LIKE \'[_\' THEN array_replace(orig, null, 0::\"char\")   ELSE orig END  ) AS outcome(ok)"," SELECT  CASE WHEN every(outcome.ok)  THEN javatest.logmessage(\'INFO\',    \'bytea passes\')  ELSE javatest.logmessage(\'WARNING\', \'bytea fails\')  END FROM  (SELECT \'\\x010203\'::bytea) AS p(orig),  (VALUES (\'\'), (\'[B\')) as q(rqcls),  roundtrip(p, rqcls) AS (class text, roundtripped bytea),  LATERAL (SELECT   (rqcls = class OR rqcls = \'\')   AND roundtripped = orig  ) AS outcome(ok)"," SELECT  CASE WHEN every(outcome.ok)  THEN javatest.logmessage(\'INFO\',    \'int2[] passes\')  ELSE javatest.logmessage(\'WARNING\', \'int2[] fails\')  END FROM  (SELECT \'{1,null,3}\'::int2[]) AS p(orig),  (VALUES (\'\'), (\'[Ljava.lang.Short;\'), (\'[S\')) as q(rqcls),  roundtrip(p, rqcls) AS (class text, roundtripped int2[]),  LATERAL (SELECT   (rqcls = class OR rqcls = \'\')   AND roundtripped =   CASE WHEN class LIKE \'[_\' THEN array_replace(orig, null, 0::int2)   ELSE orig END  ) AS outcome(ok)"," SELECT  CASE WHEN every(outcome.ok)  THEN javatest.logmessage(\'INFO\',    \'int4[] passes\')  ELSE javatest.logmessage(\'WARNING\', \'int4[] fails\')  END FROM  (SELECT \'{1,null,3}\'::int4[]) AS p(orig),  (VALUES (\'\'), (\'[Ljava.lang.Integer;\'), (\'[I\')) as q(rqcls),  roundtrip(p, rqcls) AS (class text, roundtripped int4[]),  LATERAL (SELECT   (rqcls = class OR rqcls = \'\')   AND roundtripped =   CASE WHEN class LIKE \'[_\' THEN array_replace(orig, null, 0::int4)   ELSE orig END  ) AS outcome(ok)"," SELECT  CASE WHEN every(outcome.ok)  THEN javatest.logmessage(\'INFO\',    \'int8[] passes\')  ELSE javatest.logmessage(\'WARNING\', \'int8[] fails\')  END FROM  (SELECT \'{1,null,3}\'::int8[]) AS p(orig),  (VALUES (\'\'), (\'[Ljava.lang.Long;\'), (\'[J\')) as q(rqcls),  roundtrip(p, rqcls) AS (class text, roundtripped int8[]),  LATERAL (SELECT   (rqcls = class OR rqcls = \'\')   AND roundtripped =   CASE WHEN class LIKE \'[_\' THEN array_replace(orig, null, 0::int8)   ELSE orig END  ) AS outcome(ok)"," SELECT  CASE WHEN every(outcome.ok)  THEN javatest.logmessage(\'INFO\',    \'float4[] passes\')  ELSE javatest.logmessage(\'WARNING\', \'float4[] fails\')  END FROM  (SELECT \'{1,null,3}\'::float4[]) AS p(orig),  (VALUES (\'\'), (\'[Ljava.lang.Float;\'), (\'[F\')) as q(rqcls),  roundtrip(p, rqcls) AS (class text, roundtripped float4[]),  LATERAL (SELECT   (rqcls = class OR rqcls = \'\')   AND roundtripped =   CASE WHEN class LIKE \'[_\' THEN array_replace(orig, null, 0::float4)   ELSE orig END  ) AS outcome(ok)"," SELECT  CASE WHEN every(outcome.ok)  THEN javatest.logmessage(\'INFO\',    \'float8[] passes\')  ELSE javatest.logmessage(\'WARNING\', \'float8[] fails\')  END FROM  (SELECT \'{1,null,3}\'::float8[]) AS p(orig),  (VALUES (\'\'), (\'[Ljava.lang.Double;\'), (\'[D\')) as q(rqcls),  roundtrip(p, rqcls) AS (class text, roundtripped float8[]),  LATERAL (SELECT   (rqcls = class OR rqcls = \'\')   AND roundtripped =   CASE WHEN class LIKE \'[_\' THEN array_replace(orig, null, 0::float8)   ELSE orig END  ) AS outcome(ok)"," SELECT  CASE WHEN every(outcome.ok)  THEN javatest.logmessage(\'INFO\',    \'text[] passes\')  ELSE javatest.logmessage(\'WARNING\', \'text[] fails\')  END FROM  (SELECT \'{foo,null,bar}\'::text[]) AS p(orig),  (VALUES (\'\'), (\'[Ljava.lang.String;\')) as q(rqcls),  roundtrip(p, rqcls) AS (class text, roundtripped text[]),  LATERAL (SELECT   (rqcls = class OR rqcls = \'\')   AND roundtripped = orig  ) AS outcome(ok)"})
public class TypeRoundTripper
extends Object

A class to simplify testing of PL/Java's mappings between PostgreSQL and Java/JDBC types.

Provides one function, roundTrip(). Its single input parameter is an unspecified row type, so you can pass it a row that has exactly one column of any type.

Its return type is also an unspecified row type, so you need to follow the function call with a column definition list of up to six columns. Each requested output column must have its name (case-insensitively) and type drawn from this table:

Items the roundTrip function can return

Column name	Column type	What is returned
TYPEPG	any text/varchar	The PostgreSQL type name
TYPEJDBC	any text/varchar	The JDBC Types constant
CLASSJDBC	any text/varchar	Name of the Java class JDBC claims (in metadata) it will instantiate
CLASS	any text/varchar	Name of the Java class JDBC did instantiate
TOSTRING	any text/varchar	Result of toString() on the object returned by ResultSet.getObject() (Arrays.toString if it is a primitive array, Arrays.deepToString if an array of reference type)
ROUNDTRIPPED	same as input column	Result of passing the object returned by ResultSet.getObject() directly to ResultSet.updateObject()

Serving suggestion:

SELECT
  orig = roundtripped AS good, *
FROM
  (VALUES (timestamptz '2017-08-21 18:25:29.900005Z')) AS p(orig),
  roundtrip(p) AS (roundtripped timestamptz);

Method Summary

Modifier and Type	Method	Description
static boolean	roundTrip(ResultSet in, String classname, boolean prepare, ResultSet out)	Function accepting one parameter of row type (one column, any type) and returning a row with up to six columns (use a column definition list after the function call, choose column names from TYPEPG, TYPEJDBC, CLASSJDBC, CLASS, TOSTRING, ROUNDTRIPPED where any of the first five must have text/varchar type, while ROUNDTRIPPED must match the type of the input column).

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Method Details

roundTrip

@Function(schema="javatest",
          type="RECORD",
          provides="TypeRoundTripper.roundTrip")
public static boolean roundTrip(ResultSet in,
 @SQLType(defaultValue="")
 String classname,
 @SQLType(defaultValue="false")
 boolean prepare,
 ResultSet out)
                         throws SQLException

Function accepting one parameter of row type (one column, any type) and returning a row with up to six columns (use a column definition list after the function call, choose column names from TYPEPG, TYPEJDBC, CLASSJDBC, CLASS, TOSTRING, ROUNDTRIPPED where any of the first five must have text/varchar type, while ROUNDTRIPPED must match the type of the input column).

Parameters:

in - The input row value (required to have exactly one column).

classname - Name of class to be explicitly requested (JDBC 4.1 feature) from getObject; pass an empty string (the default) to make no such explicit request. Accepts the form Class.getName would produce: canonical names or spelled-out primitives if not an array type, otherwise prefix left-brackets and primitive letter codes or Lclassname;.

prepare - Whether the object retrieved from in should be passed as a parameter to an identity PreparedStatement and the result of that be returned. If false (the default), the value from in is simply forwarded directly to out.

out - The output row (supplied by PL/Java, representing the column definition list that follows the call of this function in SQL).

Throws:

SQLException - if in does not have exactly one column, if out has more than six, if a requested column name in out is not among those recognized, if a column of out is not of its required type, or if other stuff goes wrong.