Class TypeRoundTripper


  • @SQLAction(implementor="postgresql_ge_90300",
               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 nameColumn typeWhat is returned
    TYPEPGany text/varcharThe PostgreSQL type name
    TYPEJDBCany text/varcharThe JDBC Types constant
    CLASSJDBCany text/varchar Name of the Java class JDBC claims (in metadata) it will instantiate
    CLASSany text/varchar Name of the Java class JDBC did instantiate
    TOSTRINGany 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)
    ROUNDTRIPPEDsame 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);
    

    This example relies on implementor tags reflecting the PostgreSQL version, set up in the ConditionalDDR example.

    • 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).
    • Method Detail

      • roundTrip

        @Function(schema="javatest",
                  type="RECORD",
                  provides="TypeRoundTripper.roundTrip",
                  implementor="postgresql_ge_80400")
        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.