title: How to call a stored procedure(PostgreSQL) with Spring JPA?
body: <p>PostgreSQL Procedure:</p>
  <pre><code>CREATE OR REPLACE PROCEDURE public.create_task_by_device(task_id integer, device_ids text)
   LANGUAGE plpgsql
  AS $procedure$
      begin
          -- do something here
      END;
  $procedure$
  ;
  </code></pre>
  <p>Spring JPA:(Kotlin)</p>
  <pre><code>interface TaskTemplateRepository : JpaRepository&lt;TaskTemplate, Int&gt; {
      @Procedure(&quot;public.create_task_by_device&quot;)
      fun createTaskByDevice(@Param(&quot;task_id&quot;) taskId: Int, @Param(&quot;device_ids&quot;) device_ids: String)
  }
  </code></pre>
  <p>Exception:</p>
  <pre><code>org.postgresql.util.PSQLException: ERROR: public.create_task_by_device(integer, character varying) is a procedure. To call a procedure, use CALL.
      at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) ~[postgresql-42.2.20.jar:42.2.20]
  </code></pre>
  <p>Then I got the SQL statement executed <code>{call public.create_task_by_device(?,?)}</code> by <code>spring.jpa.show-sql: true</code></p>
  <p>It seems that the statement actually executed is <code>call</code> using call.</p>
  <p>I don't understand why the whole sentence is surrounded by <code>{ ... }</code></p>
  <p>Then I tried to single-step debugging, the SQL statement actually executed in the <code>org.postgresql.jdbc.PgStatement#execute</code> method is <code>select * from create_task_by_device(1,'2') as result</code>, unlike the SQL statements output by JPA on the command line, I don't know why</p>
  <p><a href="https://i.stack.imgur.com/vT6a8.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vT6a8.png" alt="debug picture" /></a></p>
  <p>Finally, I try to write my own SQL statement</p>
  <pre><code>    @Query(&quot;call public.create_task_by_device(?1, ?2)&quot;, nativeQuery = true)
      fun createTaskByDevice(@Param(&quot;task_id&quot;) taskId: Int, @Param(&quot;device_ids&quot;) device_ids: String)
  </code></pre>
  <p>I got another exception <code>org.postgresql.util.PSQLException: No results were returned by the query.</code></p>
  <p>If I add <code>@Modifying</code> in the method, throws <code>javax.persistence.TransactionRequiredException: Executing an update/delete query</code></p>