Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB.
They only vary in their column values:
tbl_log_a
tbl_log_b
tbl_log_c
...
26 tables (from a to z). Each table has a trigger that calls a trigger function named trfn_tbl_log_%letter%
(from a
to z
) which does the exact same thing:
CREATE OR REPLACE FUNCTION trfn_tbl_log_a
RETURNS trigger AS
$BODY$
DECLARE
v_timeidx real;
BEGIN
IF NEW.timetype = 'start' THEN
SELECT timeidx FROM tbl_log_a
WHERE fnname = NEW.fnname AND timetype = 'start'
ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
IF FOUND THEN
NEW.timeidx := floor(v_timeidx) + 1;
ELSE
NEW.timeidx := 1;
END IF;
ELSIF NEW.timetype = 'lap' THEN
SELECT timeidx FROM tbl_log_a
WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'lap')
ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
IF FOUND THEN
NEW.timeidx := v_timeidx + 0.001;
ELSE
RAISE EXCEPTION USING MESSAGE = 'There is not any previous row WHERE fnname = NEW.fnname AND (timetype = start OR timetype = lap)';
END IF;
ELSIF NEW.timetype = 'resume' THEN
SELECT timeidx FROM tbl_log_a
WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'resume')
ORDER BY stmtserial DESC LIMIT 1 INTO v_timeidx;
IF FOUND THEN
NEW.timeidx := v_timeidx + 0.001;
ELSE
RAISE EXCEPTION USING MESSAGE = 'There is not any previous row WHERE fnname = NEW.fnname AND timetype = start';
END IF;
END IF;
return NEW;
END
$BODY$
LANGUAGE plpgsql;
Trigger definition:
CREATE TRIGGER trfn_tbl_log_a
BEFORE INSERT ON tbl_log_a
FOR EACH ROW EXECUTE PROCEDURE trfn_tbl_log_a();
So I have to create 26 trigger functions, one for each tbl_log_%letter%
all of them are exactly the same except for the used table name (tbl_log_a
in the example).
Is there a way to write one generic trigger function, with dynamic SQL maybe and parameterize the table name?
My trigger function uses several table columns:
timeidx
fnname
timetype
stmtserial
… and many more that I didn't add for size, but all in the example list all the kinds.
Best Answer
Assuming that, for the same trigger invocation, you take all the values from the same row in the table firing your trigger, your trigger function could look like this:
All of this should work in Postgres 9.1. But consider upgrading to a current version anyway (currently 9.4).
NEW
is not visible insideEXECUTE
. Use theUSING
clause to pass the value from the new row (NEW.fnname
).Use
TG_RELID
(orTG_TABLE_SCHEMA
andTG_TABLE_NAME
) to concatenate the table name like we worked out under your previous question:Use
format()
and dollar-quoting to simplify the syntax of string-concatenation safely.You can assign individual columns of the
NEW
row directly from the dynamic query.Use
GET DIAGNOSTICS _ct = ROW_COUNT;
to check if rows were found. Per documentation:Aside: the logic of the dynamic query is only correct for
BEFORE
trigger. AnAFTER
triggers would also see the newly inserted row.