title: Postgres: Passing custom types from Java to postgres function
body: <p>I have a postgres function which takes a complex user defined type as an input.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION addUser(user IN O_USER) RETURNS VOID
  AS
  $$
  BEGIN
  
  end;
  $$ language plpgsql;
  
  CREATE TYPE O_USER AS (
    id NUMERIC,
    name VARCHAR(50),
    addresses O_ADDRESS[]
  );
  
  
  CREATE TYPE O_ADDRESS AS (
    id NUMERIC,
    city VARCHAR(50)
  );
  </code></pre>
  
  <p>Now I need to call the function from Java.
  What I understood is that I need to pass a String which will be typecasted to the UDT by db.</p>
  
  <p>When I am passing the user without the address, then its working fine. But if I pass the array of address as well, then it is giving me the error.</p>
  
  <pre><code>ERROR:  malformed array literal: "{(1"
  DETAIL:  Unexpected end of input.
  SQL state: 22P02
  Character: 23
  </code></pre>
  
  <p>I could reproduce the issue with the postgres select statement as well. For example following works fine</p>
  
  <pre><code>Select * from addUser('(1, "Vishal", {})');
  </code></pre>
  
  <p>But when I pass the addresses, then its a problem</p>
  
  <pre><code>Select * from addUser('(1, "Vishal", {(1,"tet")})');
  Select * from addUser('(1, "Vishal", {"(1,)"})');
  Select * from addUser('(1, "Vishal", {"(1,null)"})')
  </code></pre>
  
  <p>I believe I am not able to construct the string properly. Any idea what is wrong here?</p>
  
  <p><strong>Update</strong></p>
  
  <p>Java code to call the function</p>
  
  <pre><code>import org.springframework.jdbc.core.SqlParameter;
  import org.springframework.jdbc.object.StoredProcedure;
  
  import javax.sql.DataSource;
  import java.sql.Types;
  import java.util.HashMap;
  import java.util.Map;
  
  public class AddUserProcedurePostgres extends StoredProcedure {
  
      public AddUserProcedurePostgres(DataSource dataSource) {
          super(dataSource, "addUser");
          setFunction(true);
  
          declareParameter(new SqlParameter("i_User", Types.OTHER, "O_USER"));
  
          compile();
      }
  
      public void execute() {
  
          Map&lt;String, Object&gt; input = new HashMap&lt;&gt;();
          input.put("i_User", "(1, Vishal, array[(1,tet)]::O_ADDRESS[])");
  
          execute(input);
      }
  
  }
  </code></pre>
  
  <p>Error returned by the jdbc is:</p>
  
  <pre><code>Caused by: org.postgresql.util.PSQLException: ERROR: malformed array literal: " array[(1"
    Detail: Array value must start with "{" or dimension information.
  </code></pre>