title: How to call a postgres function with an array of composite type
body: <p>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.</p>
  
  <p>I have the following composite type:</p>
  
  <pre><code>CREATE TYPE collect.event_record AS (
      event_type      integer
    , event_timestamp timestamp without time zone
    , event_data      text
    , event_import_id integer
    );
  </code></pre>
  
  <p>I have the following function:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION collect.insert_events(
      fail_on_duplicates boolean,
      source_public_id text,
      event_records collect.event_record[])
    RETURNS integer AS
    ...
  </code></pre>
  
  <p>On the postgres side everything seems to work fine. Now I just need to invoke it from java/kotlin.</p>
  
  <p>We use this version of postgres driver:</p>
  
  <pre><code>compile group: "org.postgresql", name: "postgresql", version: "9.4.1212"
  </code></pre>
  
  <p>On <code>PreparedStatement</code> there's a method that seems to be what I am looking for:</p>
  
  <pre><code>void setArray (int parameterIndex, Array x) throws SQLException;
  </code></pre>
  
  <p>The array type is <code>java.sql.Array</code> which from what I can tell can be created using the Connection object: </p>
  
  <pre><code>Array createArrayOf(String typeName, Object[] elements) throws SQLException;
  </code></pre>
  
  <p>However, here I am not sure what to put in. What should <code>typeName</code> 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 <code>java.sql.Struct</code>. </p>
  
  <p>I have been googling for examples but they seem to deal with primitive types mostly which doesn't help in my case.</p>
  
  <p>An alternative is to refactor <code>insert_events</code> to accept several arrays of primitive types, sort of a column view of my objects.</p>
  
  <p>Another alternative is to send in a JSON array which I transform into an array of <code>collect.event_record[]</code> inside the postgres function.</p>
  
  <p>However, I would like to find a way that allowed me to keep the postgres function signature I have currently.</p>
  
  <p>Any ideas are greatly appreciated.</p>