Which should I use for `DELETE ... RETURNING *` @SqlUpdate or @SqlQuery?

[Stephen Connolly]

I'm using PostgreSQL's SKIP LOCKED... e.g.

DELETE FROM task_queue 

WHERE id = (SELECT id FROM task_queue ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED) 

RETURNING *

So in my interface... do I do:

@SqlUpdate("DELETE FROM task_queue "
                  + "WHERE id = ( "
                  + "  SELECT id "
                  + "  FROM task_queue "
                  + "  ORDER BY id "
                  + "  LIMIT 1 "
                  + "  FOR UPDATE SKIP LOCKED "
                  + ") "
                  + "RETURNING *;")
Optional<Task> next();

or

@SqlQuery("DELETE FROM task_queue "
                  + "WHERE id = ( "
                  + "  SELECT id "
                  + "  FROM task_queue "
                  + "  ORDER BY id "
                  + "  LIMIT 1 "
                  + "  FOR UPDATE SKIP LOCKED "
                  + ") "
                  + "RETURNING *;")
Optional<Task> next();

And yes, i'll suck it and see... but would really like the official guidance

[Stephen Connolly]

jDBI version 3+

[Matthew Hall]

Try using @GetGeneratedKeys without naming any columns in the annotation. Postgres returns the entire affected row as generated keys.

I've used this on inserts and updates, but never tried it on a delete--let us know how it goes.

-Matthew

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Stephen Connolly]

I ended up using @SqlQuery because (apart from the case of my query where i have LIMIT 1) there is the potential to return multiple rows and I don't think @GetGeneratedKeys would handle multi-row returns... seems to be working