如何使用复合类型数组调用PostgreSQL函数

Question

我希望你能为我提供关于如何调用一个带有复合类型数组参数的Postgres函数的建议。类似问题已经被问过，但我还没有找到令人满意的答案。

我有以下复合类型：

CREATE TYPE collect.event_record AS (
    event_type      integer
  , event_timestamp timestamp without time zone
  , event_data      text
  , event_import_id integer
  );

我有以下函数：

CREATE OR REPLACE FUNCTION collect.insert_events(
    fail_on_duplicates boolean,
    source_public_id text,
    event_records collect.event_record[])
  RETURNS integer AS
  ...

在Postgres方面，一切都运作正常。现在我只需要从Java/Kotlin中调用它。

我们使用这个版本的Postgres驱动程序：

compile group: "org.postgresql", name: "postgresql", version: "9.4.1212"

PreparedStatement 上有一个方法，似乎是我正在寻找的：

void setArray (int parameterIndex, Array x) throws SQLException;

数组类型是java.sql.Array，据我所知，可以使用Connection对象创建：

Array createArrayOf(String typeName, Object[] elements) throws SQLException;

但是，我不确定应该放什么。 typeName 应该是什么？我想我应该创建一个匹配复合类型的类，并根据需要序列化字段，或者它是 java.sql.Struct 数组。

我一直在谷歌上搜索示例，但它们似乎主要处理原始类型，对我的情况没有帮助。

另一种选择是将 insert_events 重构为接受多个原始类型数组，就像我对象的列视图。

另一种选择是发送 JSON 数组，我将其转换为 postgres 函数内部的 collect.event_record[] 数组。

然而，我希望找到一种方法，可以让我保留当前的 postgres 函数签名。

非常感谢您的任何想法。

- Just another metaprogrammer

---

根据 此文档，typeName 应该确实是您想要创建数组的类型的字符串表示形式。因此，在您的情况下，它应该是 "collect.event_record"。 - Asew

Answer 1

据我所知，Postgres jdbc驱动程序不支持Struct，但对于复合类型的数组有另一种解决方法。文档says指出，可以使用以下结构创建复合类型的实例：(value_of_event_type,value_of_event_timestamp,value_of_event_data,value_event_import_id)。 event_record数据结构可以按以下方式定义：

data class EventRecord(
    val eventType: Int,
    val eventTimestamp: Instant,
    val eventData: String,
    val eventImportId: Int) {

    fun toSqlRow(): String {
        return ("(" + eventType + ","
                    + eventTimestamp + ","
                    + eventData + ","
                    + eventImportId +
                ")")
    }
}

下一步是创建PreparedStatement：

val preparedStatement = con.prepareStatement("SELECT * FROM public.insert_events(?, ?, ?)")
// ... define other parameters
preparedStatement.setArray(3, con.createArrayOf("public.event_record", records.map { e -> e.toSqlRow() }.toTypedArray()))

创建的 PreparedStatement 可用于查询数据库。

PS：当前实现不适用于包含 ,、"、' 的文本值。如果文本值包含 ,，请将其用引号括起来，并用 \" 替换 "。

- Sergii Zhevzhyk