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

Answer 2

Try something like this:

                ------------------ your connection
                V
Array inArray = conn.createArrayOf("integer", new Integer[][] {{1,10},{2,20}});
stmt.setArray(1, inArray);

A sample method you could use to build your test:

    public void testInsertMultiDimension() throws Exception {

            Connection c = getConnection();

            PreparedStatement stmt = c.prepareStatement("INSERT INTO sal_emp VALUES ('multi_Bill',?,?);");
            Array intArray = c.createArrayOf("integer", new Integer[] {1000,1000,1000,1000});
            String[][] elements = new String[2][];
            elements[0] = new String[] {"meeting_m","lunch_m"};
            elements[1] = new String[] {"training_m","presentation_m"};

            //Note - although this is a multi-dimensional array, we still supply the base element of the array
            Array multiArray = c.createArrayOf("text", elements);
            stmt.setArray(1, intArray);
            stmt.setArray(2, multiArray);
            stmt.execute();
            //Note - free is not implemented
            //myArray.free();
            stmt.close();
            c.close();
    }

Helpful links:

• Binding parameter as PostgreSQL array
• Postgres and multi-dimensions arrays in JDBC
• Passing Array from Java to Postgres

Answer 3

Your problem is PostgreSQL can use table or complex type as function's parameter or "table or complex type"'s array as function's paramter? postgresql all support. and when you create a table, it's auto create an complex type named same as tablename. like :

digoal=# create table tbl123(id int, info text);
CREATE TABLE
digoal=# select typname from pg_type  where typname='tbl123';
 typname 
---------
 tbl123
(1 row)

and you can use this type in function direct. for exp :

digoal=# create or replace function f_tbl123(i tbl123) returns tbl123 as $$  
  declare
  begin
  return i;
end;
$$ language plpgsql;
CREATE FUNCTION
digoal=# insert into tbl123 values (1,'test'),(2,'test2');
INSERT 0 2
digoal=# select f_tbl123(t) from tbl123 t;
 f_tbl123  
-----------
 (1,test)
 (2,test2)
(2 rows)

the array is also can used in postgresql function. if you don't known how array construct in java, i think this exp can help you.

digoal=# select (unnest('{"(1,abc)","(2,ww)"}'::tbl123[])).*;
 id | info 
----+------
  1 | abc
  2 | ww
(2 rows)

digoal=# select '{"(1,abc)","(2,ww)"}'::tbl123[];
        tbl123        
----------------------
 {"(1,abc)","(2,ww)"}
(1 row)
digoal=# select array['(1,abc)','(2,ww)'];
        array         
----------------------
 {"(1,abc)","(2,ww)"}
(1 row)

digoal=# select array['(1,abc)','(2,ww)']::tbl123[];
        array         
----------------------
 {"(1,abc)","(2,ww)"}
(1 row)
digoal=# select (unnest(array['(1,abc)','(2,ww)'])::tbl123).*;
 id | info 
----+------
  1 | abc
  2 | ww
(2 rows)