Executing multiple sql commands with one generated function

[krishna15898]

My table of items looks like this.

ID	Name	Position
105	apple	1
107	ball	2
110	cat	3
111	dog	4

I am writing a delete API which deletes an item and should update the positions of the remaining items so there are no gaps. For example, after I delete ball, I would like the table to look like

ID	Name	Position
105	apple	1
110	cat	2
111	dog	3

In other words - it should delete the target item and reduce the position of all the items with positions greater than that of the target item by one.

Below is the way I am currently doing it -

-- GetItem: one
SELECT * from items WHERE id=?;

-- name: DeleteItem:execrows
DELETE FROM items WHERE id = ?;

-- name: UpdatePositions: execrows
UPDATE items SET position = position - 1
WHERE item.position > sqlc.arg(position);

And calling the functions shown -

item, _ := GetItem(id)
_, _ := DeleteItem(id)
_, _ := UpdatePositions(item.position)

I am first getting the item to be deleted to get its position, deleting the item, and executing an update command with the position found in the first step. I want to reduce the function calls I make for this delete operation.

Questions -

1. Can I execute the DELETE and UPDATE commands using a single generated function? So, execute two SQL statements with one generated function?
2. Can I omit the GetItem call by writing a single SQL statement to get and update item positions?

[kyleconroy]

To execute multiple queries in a single function, you'll want to use a database transaction. Executing multiple queries outside of a transaction may result in race conditions, so you'll want to do this anyways.

If you're using PostgreSQL, you don't need the GetItem query. Instead you can use RETURNING. But if you're using MySQL, you're going to need to use the three query approach.

-- name: DeleteItem :one
DELETE FROM items WHERE id = ?
RETURNING position;

-- name: UpdatePositions :execrows
UPDATE items SET position = position - 1
WHERE item.position > sqlc.arg(position);