Re: On Insert Do Instead Select....

Daniel Jeffery wrote on 30.12.2011 01:30:
> Is it at all possible for an executed insert statement to return a
> select statement resultset?
>
> Here's an example table:
>
> /CREATE TABLE test (/ / id integer NOT NULL DEFAULT nextval(
> 'test_id_seq'::regclass ),/ / column_name text/ /);/ / / / / Here's
> the table's rule:
>
> /CREATE RULE replace_test AS ON INSERT TO test WHERE EXISTS( SELECT 1
> FROM test WHERE (column_name = NEW.column_name) ) DO INSTEAD SELECT
> test.id <http://test.id> FROM test WHERE column_name =
> NEW.column_name;/ / / / / How would I get the JDBC driver to return
> the resultset?

I haven't tried it, but using Statement.execute() (instead of executeUpdate()) should do it.

Then you need to call getMoreResults(), getResults(), getUpdateCount() to get everything.

Pay attention to the javadocs of Statement.getUpdateCount() and Statement.getMoreResults() describing the condition how to detect if there are no more results/update counts to retrieve.

It's essentially the same as retrieving the data returned by an INSERT ... RETURNING.