In this audit table you can opt to store exactly one row. One can
ensure this by running the following as a unit:
TRUNCATE audit; INSERT INTO audit VALUES (5, 'daniel');
Then, one can change the trigger to reference that table.
That's the gist of it. Here are embellishments and additional
thoughts that may involve more invasive but also potentially helpful
changes to the module:
TRUNCATE is scary, because it deletes everything in a
table. One should take pains to ensure that one does not truncate
an important table.
To avoid truncate table and get some potentially useful behavior,
consider CREATE TEMPORARY TABLE ... ON COMMIT DELETE ROWS; This
causes the audit table to be depopulated on every COMMIT; which may
help prevent error.
Another that is interesting is CREATE TEMPORARY TABLE ... ON COMMIT
DROP;. The downside is that temporary table creation tends be a bit
more expensive than temporary table truncation.
A slightly different approach would be to use CREATE OR REPLACE
FUNCTION in the pg_temp namespace, which has the effect of creating a
'temporary function'.
schubert
commented
12 years ago
As suggested by Heroku's database staff:
CREATE TEMPORARY TABLE audit (user_id integer, user_name text);
In this audit table you can opt to store exactly one row. One can ensure this by running the following as a unit: TRUNCATE audit; INSERT INTO audit VALUES (5, 'daniel');
Then, one can change the trigger to reference that table.
That's the gist of it. Here are embellishments and additional thoughts that may involve more invasive but also potentially helpful changes to the module: