I have a postgres 'update' query in my business flow and a requirement from a stakeholder to produce some more visible bookkeeping when the update is successful, in the form of "insert a line in this other table" (...let's not debate the reasons/wisdom behind this requirement).
I am currently implementing a composition of queries in application code:
update -> async returns to server -> was the count > 0 -> insert -> ok/ error handle + retry.
Since the above is not the most efficient (2 trips to the database, potential network issues, error handling + retries involved), can it potentially be issued as a single transaction? (The issue here being that an update of 0 rows is not an error, which further complicates things).
pseudo-code
BEGIN;
UPDATE ....;
IF (update_count > 0) THEN
INSERT ...;
COMMIT;
ELSE
ROLLBACK or RAISE;
I am not particularly familiar with CASE, WITH count AS (UPDATE) or RAISE, to be able to compose the SQL I need, assuming a solution like the one I am describing is even workable?
PostgreSQL has a RETURNING clause that returns rows affected by an UPDATE or DELETE. Couple this with CTEs, and you can accomplish your objective in a single instruction:
begin;
with do_update as (
update some_table
set a = 1, b = 2, c =3
where id = 999
returning *
)
insert into audit_table
(affected_table, updated_at, update_user)
select distinct 'some_table', now(), current_user
from do_update
;
commit;
The INSERT statement has access to the results of the UPDATE through the CTE name do_update. If no rows were updated, then nothing gets inserted into audit_table. If one or more rows are updated, then the DISTINCT causes a single row to be inserted into audit_table.