9

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.

Improve this question
5
  • 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. Commented Jan 9, 2015 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? Commented Jan 9, 2015 at 14:47
  • 1
    JdbcTemplate.execute(String) is designed for DDL statements. docs.spring.io/spring-framework/docs/current/javadoc-api/org/… -- use one of the others execute(String, ResultSetExtractor|RowCallbackHandler|RowMapper) & simply discard the results. Commented Jan 9, 2015 at 15:00
  • You didn't mention that you were using Spring. My comment relates to plain JDBC. Commented Jan 9, 2015 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. Commented Jan 9, 2015 at 15:10

2 Answers 2

Reset to default
4

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. There is a CALL statement, but that only works for procedures, not functions.

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 use executeUpdate(), 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)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

My PL is from stackoverflow.com/questions/1109061/…, 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 That doesn't matter, SELECT will always return data (in case of RETURNS void functions, it will return a single NULL).
@membersound in case of JDBC, do not use executeUpdate(), just use execute() like with any other SELECT statement
NB: since version 11, PostgreSQL does support CALL
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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.