Trigger function using current row and current table name as variables

Question

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.

• Erwin Brandstetter: This is a follow-up to: dba.stackexchange.com/questions/112184/… and is followed by: dba.stackexchange.com/questions/112324/…

Answer 1

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:

CREATE OR REPLACE FUNCTION trfn_tbl_log_any()
 RETURNS trigger AS
$func$
DECLARE
 _ct int;
BEGIN
IF NEW.timetype = 'start' THEN
 EXECUTE format($$
 SELECT floor(t.timeidx) + 1
 FROM %s t
 WHERE t.fnname = 1ドル
 AND t.timetype = 'start'
 ORDER BY t.stmtserial DESC
 LIMIT 1$$
 , TG_RELID::regclass -- concatenate *identifer* ..
 )
 USING NEW.fnname -- .. but pass *value* in USING clause
 INTO NEW.timeidx;
 GET DIAGNOSTICS _ct = ROW_COUNT;
 IF _ct > 0 THEN -- do nothing
 ELSE
 NEW.timeidx := 1;
 END IF;
END IF;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;

All of this should work in Postgres 9.1. But consider upgrading to a current version anyway (currently 9.4).

• NEW is not visible inside EXECUTE. Use the USING clause to pass the value from the new row (NEW.fnname).

• Use TG_RELID (or TG_TABLE_SCHEMA and TG_TABLE_NAME) to concatenate the table name like we worked out under your previous question:

  • PostgreSQL trigger function that selects from %current% table

• 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:

  Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

Aside: the logic of the dynamic query is only correct for BEFORE trigger. An AFTER triggers would also see the newly inserted row.

• Natysiu16: Thanks @Erwin Brandstetter, so it can be applied, could you please tell me why you use SELECT floor(t.timeidx) + 1 in my script I only added it if ... IF FOUND THEN where is it in your example?, _ct means ROW COUNT? why the _"underscore" before it?, FROM %s t means from table? %s means string? In WHERE t.fnname = 1ドル what does 1ドル mean? what Im supposed to add after , t.fnname -- + 1 ?? -- more? sorry do you have any link tutorial about that syntax you used?, I just can't recognize my script logic, in the script you provide, thanks for your support.
• Erwin Brandstetter: 1. If the SELECT finds no row, nothing is assigned, so I simplified to a single step where your original first assigns a variable and then assigns NEW.timeidx. 2. I removed the "more .." parts now - was trying to address the unclear part, assuming you want to assign more than one columns. 3. Read the manual or find related answer here or on SO