Java – PostgreSQL JDBC table valued parameter

MSSQL has a good function called table - valued parameter It allows you to pass tables of custom data to stored procedures and functions

I want to know what is equivalent in PostgreSQL. If it exists, use JDBC? I know the option of passing arrays as function parameters, but this seems to be 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 use the jdbc driver to call this function with an array of user-defined data types?

Solution

Suppose you want to pass values from the client If a value already exists in the database, there are other simpler methods

composite_ Syntax of type array

What you can pass seems to be limited by Java types and JDBC types, and there seems to be no provision for array types, rather than arrays of composite values

However, you can pass the text representation at any time I am establishing two basic facts:

1)Per documentation:

Boldly emphasize my, so you created the type number defined in your question_ with_ After time, or if you define a table with the same column, it will automatically register the corresponding composite type in the system - you can also automatically use the array type as number_ with_ time [].

2) Each value has a text representation

Therefore, there is another text_ with_ Text representation of 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 value defined in your function - which is hidden in your problem

To avoid the complexity of array processing in JDBC, pass a text representation Create a function that accepts text arguments

I won't use the name "date" as a timestamp Use this slightly adjusted type definition:

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

Simple SQL functions:

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

call:

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

This SQL fiddle Demo:

>The above SQL functions > PL / PgSQL variants > several syntax variants of compound type array > function call

Call the function like any other function, using simple text parameters:

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.

Return rows from a PL / PgSQL function