Package org.postgresql.pljava.example.annotation

Class Variadic

  • @SQLAction(requires={"sumOfSquares","sumOfSquaresBoxed"},
           install={"CREATE FUNCTION javatest.javaformat(  format pg_catalog.text,  VARIADIC args pg_catalog.anyarray  DEFAULT CAST(ARRAY[] AS pg_catalog.text[])) RETURNS pg_catalog.text RETURNS NULL ON NULL INPUT LANGUAGE java AS \'java.lang.String=     java.lang.String.format(java.lang.String,java.lang.Object[])\'","COMMENT ON FUNCTION javatest.javaformat( pg_catalog.text, VARIADIC pg_catalog.anyarray) IS \'Invoke Java\'\'s String.format with a format string and any number of arguments. This is not quite as general as the Java method implies, because, while the variadic argument is declared \'\'anyarray\'\' and its members can have any type, PostgreSQL requires all of them to have the same type in any given call. Furthermore, in the VARIADIC anyarray case, as here, the actual arguments must not all have \'\'unknown\'\' type; if supplying bare literals, one must be cast to a type. PostgreSQL will not recognize a call of a variadic function unless at least one argument to populate the variadic parameter is supplied; to allow calls that don\'\'t pass any, give the variadic parameter an empty-array default, as done here.\'","SELECT  CASE   WHEN s.ok AND d.ok   THEN javatest.logmessage(\'INFO\', \'variadic calls ok\')   ELSE javatest.logmessage(\'WARNING\', \'variadic calls ng\')  END FROM  (SELECT    pg_catalog.every(expect IS NOT DISTINCT FROM got)   FROM    (VALUES     (      \'Hello, world\',      javatest.javaformat(\'Hello, %s\', \'world\'::text)     )    ) AS t(expect, got)  ) AS s(ok),  (SELECT    pg_catalog.every(expect IS NOT DISTINCT FROM got)   FROM    (VALUES     (14.0, javatest.sumOfSquares(1, 2, 3)),     (14.0, javatest.sumOfSquares(1, 2, null, 3)),     ( 0.0, javatest.sumOfSquares()),     (14.0, javatest.sumOfSquaresBoxed(1, 2, 3)),     (null, javatest.sumOfSquaresBoxed(1, 2, null, 3))    ) AS t(expect, got)  ) AS d(ok)"},
           remove="DROP FUNCTION javatest.javaformat(pg_catalog.text,anyarray)")
public class Variadic
extends Object
    Provides example methods to illustrate variadic functions.

    The key is the @Function annotation declaring the function variadic to PostgreSQL. The Java method parameter is declared as an ordinary array, not with Java's ... syntax; in fact, that would be impossible for a function with a composite return type (where the Java signature would have to include a ResultSet parameter after the variadic input parameter).

    • Method Detail

      • sumOfSquaresBoxed

        @Function(schema="javatest",
          effects=IMMUTABLE,
          onNullInput=RETURNS_NULL,
          variadic=true,
          provides="sumOfSquaresBoxed")
public static Double sumOfSquaresBoxed​(Double[] vals)
        Compute a double-precision sum of squares, returning null if any input value is null.

        The RETURNS_NULL annotation does not mean the array collecting the variadic arguments cannot have null entries; it only means PostgreSQL will never call this function with null for the array itself.

      • sumOfSquares

        @Function(schema="javatest",
          effects=IMMUTABLE,
          onNullInput=RETURNS_NULL,
          variadic=true,
          provides="sumOfSquares")
public static double sumOfSquares​(@SQLType(defaultValue={})
                                  double[] vals)
        Compute a double-precision sum of squares, treating any null input as zero.

        The RETURNS_NULL annotation does not mean the array collecting the variadic arguments cannot have null entries; it only means PostgreSQL will never call this function with null for the array itself. Because the Java parameter type here is primitive and cannot represent nulls, PL/Java will have silently replaced any nulls in the input with zeros.

        This version also demonstrates using @SQLType to give the variadic parameter an empty-array default, so PostgreSQL will allow the function to be called with no corresponding arguments. Without that, PostgreSQL won't recognize a call to the function unless at least one argument corresponding to the variadic parameter is supplied.