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.