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 byResultSet.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 toResultSet.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).
-
-
-
Method Detail
-
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) fromgetObject
; pass an empty string (the default) to make no such explicit request. Accepts the formClass.getName
would produce: canonical names or spelled-out primitives if not an array type, otherwise prefix left-brackets and primitive letter codes orL
classname;
.prepare
- Whether the object retrieved fromin
should be passed as a parameter to an identityPreparedStatement
and the result of that be returned. If false (the default), the value fromin
is simply forwarded directly toout
.out
- The output row (supplied by PL/Java, representing the column definition list that follows the call of this function in SQL).- Throws:
SQLException
- ifin
does not have exactly one column, ifout
has more than six, if a requested column name inout
is not among those recognized, if a column ofout
is not of its required type, or if other stuff goes wrong.
-
-