What we're going to do here is, essentially, make a row store that
lasts for the length of the statement and populate same with a
statement-level trigger. The row store is about as light a weight as
something not built into PostgreSQL could be. There's some
housekeeping, too, and that's where the invention came in.
First, let's make a base table, operations on which we'd like to summarize:
BEGIN;
CREATE TABLE a(
i INT
);
Next, let's set up the temporary row store we'll be using each
time we write. First the trigger function, then the trigger itself:
CREATE FUNCTION set_up_rows()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS a_rows(i INT) ON COMMIT DROP;
RETURN NULL;
END;
$$;
CREATE TRIGGER statement_before_writing_a
BEFORE INSERT OR UPDATE OR DELETE ON a
FOR EACH STATEMENT
EXECUTE PROCEDURE set_up_rows();
Our row store is in place. Let's make a per-row trigger to populate it.
CREATE FUNCTION stash_a_row_deltas()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO a_rows(i)
VALUES(
CASE WHEN TG_OP = 'INSERT' THEN NEW.i
WHEN TG_OP = 'UPDATE' THEN NEW.i - OLD.i
ELSE -1 * OLD.i
END
);
IF TG_OP IN ('INSERT','UPDATE')
THEN RETURN NEW;
ELSE RETURN OLD;
END IF;
END;
$$;
CREATE TRIGGER row_deltas_while_writing_a
BEFORE INSERT OR UPDATE OR DELETE ON a
FOR EACH ROW
EXECUTE PROCEDURE stash_row_deltas();
Now, to summarize
and clean up:
CREATE FUNCTION summarize_a_rows()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
DECLARE the_sum BIGINT;
BEGIN
SELECT INTO the_sum sum(i) FROM a_rows;
RAISE NOTICE 'Total change: %.', the_sum;
TRUNCATE a_rows;
RETURN NULL;
END;
$$;
CREATE TRIGGER statement_after_writing_a
AFTER INSERT OR UPDATE OR DELETE ON a
FOR EACH STATEMENT
EXECUTE PROCEDURE summarize_rows();
Now let's see what's going on:
statement> INSERT INTO a VALUES (1), (2), (3), (4), (5);
NOTICE: Total change: 15.
INSERT 0 5
statement> UPDATE a SET i=i+1;
NOTICE: relation "a_rows" already exists, skipping
CONTEXT: SQL statement "CREATE TEMPORARY TABLE IF NOT EXISTS a_rows(i INT) ON COMMIT DROP"
PL/pgSQL function set_up_rows() line 3 at SQL statement
NOTICE: Total change: 5.
UPDATE 5
statement> ROLLBACK;
Last, the entire wonk of code to cut, paste and ponder:
BEGIN;
CREATE TABLE a(
i INTEGER
);
CREATE FUNCTION set_up_a_rows()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS a_rows(LIKE a) ON COMMIT DROP;
RETURN NULL;
END;
$$;
CREATE TRIGGER statement_before_writing_a
BEFORE INSERT OR UPDATE OR DELETE ON a
FOR EACH STATEMENT
EXECUTE PROCEDURE set_up_a_rows();
CREATE OR REPLACE FUNCTION stash_a_row_deltas()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO a_rows(i)
VALUES(
CASE TG_OP
WHEN 'INSERT' THEN COALESCE(NEW.i,0)
WHEN 'UPDATE' THEN COALESCE(NEW.i,0) - COALESCE(OLD.i,0)
WHEN 'DELETE' THEN -1 * COALESCE(OLD.i,0)
END
);
IF TG_OP IN ('INSERT','UPDATE')
THEN
RETURN NEW;
ELSE
RETURN OLD;
END IF;
END;
$$;
CREATE TRIGGER during_trg
BEFORE INSERT OR UPDATE OR DELETE ON a
FOR EACH ROW
EXECUTE PROCEDURE stash_a_row_deltas();
CREATE FUNCTION summarize_a_rows()
RETURNS TRIGGER LANGUAGE plpgsql
AS $$
DECLARE the_sum BIGINT;
BEGIN
SELECT INTO the_sum sum(i) FROM a_rows;
RAISE NOTICE 'Total change: %.', the_sum;
TRUNCATE a_rows;
RETURN NULL;
END;
$$;
CREATE TRIGGER statement_after_writing_a
AFTER INSERT OR UPDATE OR DELETE ON a
FOR EACH STATEMENT
EXECUTE PROCEDURE summarize_a_rows();
INSERT INTO a(i)
SELECT * FROM generate_series(1,10000);
UPDATE a SET i=i+1;
ROLLBACK;
