Postgresql JDBC Table Valued Parameters

Question

MSSQL has a great feature called Table Valued Parameters. It allows you to pass a table of a custom data to stored procedures and functions.

I was wondering what is the equivalent in PostgreSQL, if one exists, using JDBC? I know about the option of passing arrays as function parameters, but that seems limited to PostgreSQL data types.

Consider the following PL/pgSQL code:

CREATE  TYPE number_with_time AS(
_num   float,
_date  timestamp
);

and this function header:

CREATE OR REPLACE FUNCTION myfunc(arr number_with_time[])

Can anyone post a Java code using JDBC driver of calling that function with an array of the user defined data type?

Answer 1

Assuming you want to pass values from the client. If the values exist in the database already there are other, simpler ways.

Syntax for array of composite_type

I know about the option of passing arrays as function parameters, but that seems limited to PostgreSQL data types.

What you can pass seems to be limited by Java Types and JDBC Types, and there does not seem be provisions for array types, not to speak of arrays of composite values ...

However, you can always pass a text representation. I am building on two basic facts:

1. Quoting the manual:

Arrays of any built-in or user-defined base type, enum type, or composite type can be created. Arrays of domains are not yet supported.

Bold emphasis mine. Therefore, after you have created the type number_with_time as defined in your question, or defined a table with the same columns which registers the row type in the system automatically, you can also use the array type number_with_time[].

2. There is a text representation for every value.

Therefore, there is also a text representation for number_with_time[]:

'{"(1,2014-04-20 20:00:00)","(2,2014-04-21 21:00:00)"}'::number_with_time[]

Function call

The actual function call depends on the return values defined in your function - which is hidden in your question.

To avoid complications from array handling in JDBC, pass the text representation. Create the function taking a text parameter.

I am not going to use the name "date" for a timestamp. Working with this slightly adjusted type definition:

CREATE TYPE number_with_time AS(
   _num float
 , _ts  timestamp
);

Simple SQL function:

CREATE OR REPLACE FUNCTION myfunc_sql(_arr_txt text)
  RETURNS integer       -- example
  LANGUAGE sql AS
$func$
   SELECT sum(_num)::int
   FROM   unnest (_arr_txt::number_with_time[]) x
   WHERE  _ts > '2014-04-19 20:00:00';
$func$;

Call:

SELECT myfunc_sql('{"(1,2014-04-20 20:00:00)","(2,2014-04-21 21:00:00)"}');

db<>fiddle here
_{Old sqlfiddle}

Demonstrating:

• above SQL function
• PL/pgSQL variant
• a couple of syntax variants for the array of composite type
• the function calls

Call the function like any other function taking a simple text parameter:

CallableStatement myProc = conn.prepareCall("{ ? = call myfunc_sql( ? ) }");
myProc.registerOutParameter(1, Types.VARCHAR);
// you have to escape double quotes in a Java string!
myProc.setString(2, "{\"(1,2014-04-20 20:00:00)\",\"(2,2014-04-21 21:00:00)\"}");
myProc.execute();
String mySum = myProc.getInt(1);
myProc.close(); 

Details in the Postgres JDBC manual here.

Example to return a whole table via JDBC:

• Return rows from a PL/pgSQL function