Re: CommandStatus from insert returning when using a portal.

On Wed, Jul 12, 2023 at 5:57 PM Dave Cramer <davecramer@gmail.com> wrote:

On Wed, 12 Jul 2023 at 20:00, <chap@anastigmatix.net> wrote:

Dave Cramer <davecramer@gmail.com> writes:
> Obviously I am biased by the JDBC API which would like to have
> PreparedStatement.execute() return the number of rows inserted
> without having to wait to read all of the rows returned

Huh ... just how *is* PreparedStatement.execute() supposed
to behave when the statement is an INSERT RETURNING?

It's really executeUpdate which is supposed to return the number of rows updated.

Right, and executeUpdate is the wrong API method to use, in the PostgreSQL world, when executing insert/update/delete with the non-SQL-standard returning clause.  executeQuery is the method to use.  And execute() should behave as if executeQuery was called, i.e., return true, which it is capable of doing since it has resultSet data that it needs to handle.

The addition of returning turns the insert/update/delete into a select in terms of effective client-seen behavior.

ISTM that you are trying to make user-error less painful.  While that is laudable it apparently isn't practical.  They can either discard the results and get a count by omitting returning or obtain the result and derive the count by counting rows alongside whatever else they needed the returned data for.