6

I created the following function for my postgresql:

CREATE FUNCTION myfunc(param INT) RETURNS VOID AS..

How can I call that function without SELECT? When I run SELECT myfunc(1) it will work, even though it does not return anything by design.

But I want to run it without the select, just myfunc(1), which does not work and tells me Syntax error at 1.

I'm trying to implement the db_merge function from Insert, on duplicate update in PostgreSQL?.

I cannot run SELECT db_merge(..) from java as this will give me a "A result was returned when none was expected."), PSQLState.TOO_MANY_RESULTS)error.

Community
  • 1
  • 1
membersound
  • 81,582
  • 193
  • 585
  • 1,120
  • 2
    "*I cannot run SELECT db_merge(..) from java*" - that's not true. `Statement.execute("select db_merge()")` will work just fine. You just can't use `executeUpdate()` because that's not allowed to return something. –  Jan 09 '15 at 14:44
  • @a_horse_with_no_name I running it within a `executeBatch()` from statement, which is called by `JdbcTemplate.batchUpdate()`. So I could probably not use that? – membersound Jan 09 '15 at 14:47
  • 1
    `JdbcTemplate.execute(String)` is designed for DDL statements. http://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html#execute-java.lang.String- -- use one of the others `execute(String, ResultSetExtractor|RowCallbackHandler|RowMapper)` & simply discard the results. – pozs Jan 09 '15 at 15:00
  • You didn't mention that you were using Spring. My comment relates to plain JDBC. –  Jan 09 '15 at 15:02
  • In JDBC you can use the `{call myfunc()}` escape, but it just does a `SELECT` behind the scenes anyway. I fail to see what the problem with this is. – Craig Ringer Jan 09 '15 at 15:10

2 Answers2

2

You cannot do that (you cannot call a stored function without issuing a SELECT statement in PostgreSQL). Also, PostgreSQL does not support the CALL statement.

More about calling functions in PostgreSQL.

Note: within PL/pgSQL context, you have to use PERFORM (instead of SELECT) if your called function has RETURNS void.

Edit:

In case of JDBC, do not useexecuteUpdate(), just use execute() like with any other SELECT statement.

In case of Spring's JdbcTemplate: execute(String) is designed for DDL statements; use one of the others & simply discard the results:

  • query(String, ResultSetExtractor)
  • query(String, RowCallbackHandler)
  • query(String, RowMapper)
pozs
  • 34,608
  • 5
  • 57
  • 63
  • My PL is from http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/1109198#1109198, and does only have `UPDATE` and `INSERT`. How could I else execute the function in postgresql without the select, as the function should return void? What are my chances? – membersound Jan 09 '15 at 14:39
  • @membersound That doesn't matter, `SELECT` will always return data (in case of `RETURNS void` functions, it will return a single `NULL`). – pozs Jan 09 '15 at 14:43
  • 3
    @membersound in case of JDBC, do not use `executeUpdate()`, just use `execute()` like with any other `SELECT` statement – pozs Jan 09 '15 at 14:45
0

The Postgres messaging protocol allows for direct function invocation, and the Postgres JDBC driver exposes this through the Fastpath class. The driver documentation explains how to set it up.

Nick Barnes
  • 19,816
  • 3
  • 51
  • 63