Tengo una consulta de 'actualización' de postgres en mi flujo comercial y un requisito de una parte interesada para producir una contabilidad más visible cuando la actualización sea exitosa, en forma de "insertar una línea en esta otra tabla" (... no discutamos las razones/la sabiduría detrás de este requisito).
Actualmente estoy implementando una composición de consultas en el código de la aplicación: update -> async returns to server -> was the count > 0 -> insert -> ok/ error handle + retry .
Dado que lo anterior no es lo más eficiente (2 viajes a la base de datos, posibles problemas de red, manejo de errores + reintentos involucrados), ¿puede emitirse potencialmente como una sola transacción? (El problema aquí es que una actualización de 0 filas no es un error, lo que complica aún más las cosas).
pseudo-code BEGIN; UPDATE ....; IF (update_count > 0) THEN INSERT ...; COMMIT; ELSE ROLLBACK or RAISE; No estoy particularmente familiarizado con CASE , WITH count AS (UPDATE) o RAISE , para poder componer el SQL que necesito, suponiendo que una solución como la que estoy describiendo sea viable.
PostgreSQL tiene una cláusula RETURNING que devuelve las filas afectadas por UPDATE o DELETE . Combine esto con CTE y podrá lograr su objetivo en una sola instrucción:
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; La declaración INSERT tiene acceso a los resultados de la UPDATE a través del nombre CTE do_update . Si no se actualizaron filas, no se inserta nada en audit_table . Si se actualizan una o más filas, DISTINCT hace que se inserte una sola fila en audit_table .