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.

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.