后格雷 sql JDBC 表值参数

arrays postgresql java jdbc plpgsql

2022-09-04 22:44:50

MSSQL有一个很棒的功能，叫做表值参数。它允许您将自定义数据表传递给存储过程和函数。

我想知道PostgreSQL中的等效物是什么，如果存在的话，使用JDBC？我知道将数组作为函数参数传递的选项，但这似乎仅限于PostgreSQL数据类型。

请考虑以下 PL/pgSQL 代码：

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

和此函数标头：

CREATE OR REPLACE FUNCTION myfunc(arr number_with_time[])

任何人都可以使用JDBC驱动程序发布Java代码，该驱动程序使用用户定义的数据类型数组调用该函数？

答案 1

假设您要从客户端传递值。如果数据库中已经存在这些值，则还有其他更简单的方法。

composite_type数组的语法

我知道将数组作为函数参数传递的选项，但这似乎仅限于PostgreSQL数据类型。

您可以传递的内容似乎受到Java类型和JDBC类型的限制，并且似乎没有数组类型的规定，更不用说复合值的数组了......

但是，您始终可以传递表示形式。我基于两个基本事实：text

引用手册：

可以创建任何内置或用户定义的基类型、枚举类型或复合类型的数组。尚不支持域数组。

大胆强调我的。因此，在创建了问题中定义的类型，或者定义了具有在系统中自动注册行类型的相同列的表之后，还可以使用数组类型。number_with_timenumber_with_time[]

每个值都有一个文本表示形式。

因此，还有一个文本表示形式：number_with_time[]

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

函数调用

实际的函数调用取决于函数中定义的返回值 - 该值隐藏在问题中。

要避免在 JDBC 中处理数组的复杂性，请传递表示形式。创建采用参数的函数。texttext

我不打算使用名称“date”作为.使用此略微调整的类型定义：timestamp

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

简单的 SQL 函数：

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$;

叫：

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

db<>在这里小提琴
_{Old sqlfiddle}

展示：

以上 SQL 函数
PL/pgSQL 变体
复合类型数组的几个语法变体
函数调用

像调用任何其他函数一样调用该函数，并采用简单的参数：text

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();

Postgres JDBC手册中的详细信息请点击此处。

通过 JDBC 返回整个表的示例：

从 PL/pgSQL 函数返回行

答案 2

试试下面这样：

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

可用于构建测试的示例方法：

    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();
    }

有用的链接：