How to call a postgres function with an array of composite type

Question

I am looking for suggestions on how to call a postgres function that has an argument that is an array of composite type. Similar questions have been asked but I have found a satisfactory answer.

I have the following composite type:

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

I have the following function:

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

On the postgres side everything seems to work fine. Now I just need to invoke it from java/kotlin.

We use this version of postgres driver:

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

On PreparedStatement there's a method that seems to be what I am looking for:

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

The array type is java.sql.Array which from what I can tell can be created using the Connection object:

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

However, here I am not sure what to put in. What should typeName be? I imagine that I should create a class that matches the composite type and that I serialize the fields as necessary or it's an array of java.sql.Struct.

I have been googling for examples but they seem to deal with primitive types mostly which doesn't help in my case.

An alternative is to refactor insert_events to accept several arrays of primitive types, sort of a column view of my objects.

Another alternative is to send in a JSON array which I transform into an array of collect.event_record[] inside the postgres function.

However, I would like to find a way that allowed me to keep the postgres function signature I have currently.

Any ideas are greatly appreciated.

From this doc typeName should indeed be the string representation of the type you want to make an array of. So in your case it should be "collect.event_record". — Asew
– Asew, Commented Jun 22, 2017 at 11:37

Sergii Zhevzhyk · Accepted Answer · 2018-07-16 17:39:52Z

As far as I know the Postgres jdbc driver doesn't support Struct, but there is another workaround for arrays of composite types. The documentation says that it is possible to create an instance of a composite type using the following construction: (value_of_event_type,value_of_event_timestamp,value_of_event_data,value_event_import_id). The event_record data structure can be defined in the following way:

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

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

The next step is to create 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()))

Created PreparedStatement can be used to query the database.

PS: The current implementation will not work for text values containing ,, ", '. Wrap the text value in quotes if it contains , and replace " with \".

Collectives™ on Stack Overflow

How to call a postgres function with an array of composite type

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related