Postgresql – Trigger function using current row and current table name as variables (final part)

dynamic-sqlplpgsqlpostgresqlpostgresql-9.1trigger

Like detailed in my first question I have a Postgres 9.1 database with multiple tables that have the exact same column names, 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_timetypespan()
  RETURNS trigger AS
$BODY$
DECLARE
v_timetype character varying;
v_timestmp_timetype timestamp without time zone;
v_timetypespan_resume interval;
v_stmtserial real;
v_sumtimetypespan_fnname interval;

BEGIN

IF NEW.timetype = 'lap' THEN
  SELECT timetype, timestmp, timetypespan FROM tbl_log_a WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'resume') ORDER BY stmtserial DESC LIMIT 1 INTO v_timetype, v_timestmp_timetype, v_timetypespan_resume;
    IF v_timetype = 'start' THEN
      NEW.timetypespan := age(NEW.timestmp, v_timestmp_timetype);
    ELSIF v_timetype = 'resume' THEN
      SELECT timestmp FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'start' ORDER BY stmtserial DESC LIMIT 1 INTO v_timestmp_timetype;
      NEW.timetypespan := age(NEW.timestmp, v_timestmp_timetype) - v_timetypespan_resume;
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
    END IF;

ELSIF NEW.timetype = 'resume' THEN
  SELECT timestmp FROM tbl_log_a WHERE fnname = NEW.fnname AND (timetype = 'start' OR timetype = 'lap') ORDER BY stmtserial DESC LIMIT 1 INTO v_timestmp_timetype;
    IF FOUND THEN
      NEW.timetypespan := age(NEW.timestmp, v_timestmp_timetype);
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
    END IF;

ELSIF NEW.timetype = 'total' THEN
  SELECT stmtserial FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'total' ORDER BY stmtserial DESC LIMIT 1 INTO v_stmtserial;
  SELECT SUM(timetypespan) FROM (SELECT DISTINCT ON (floor(timeidx)::int) floor(timeidx)::int timeidx, timetypespan
  FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'lap' AND stmtserial > coalesce(v_stmtserial, 0) ORDER BY 1, 2 DESC) a INTO v_sumtimetypespan_fnname;
    IF v_sumtimetypespan_fnname NOTNULL THEN
      NEW.timetypespan := v_sumtimetypespan_fnname;
    ELSE
      RAISE EXCEPTION USING MESSAGE = 'There is not any previous row...';
    END IF;

END IF;
return NEW;

END
$BODY$
  LANGUAGE plpgsql VOLATILE;

Trigger definition:

CREATE TRIGGER trfn_tbl_log_a_timetypespan
  BEFORE INSERT ON tbl_log_a
  FOR EACH ROW EXECUTE PROCEDURE trfn_tbl_log_a_timetypespan();

So I have to create 26 trigger functions, one for each tbl_log_%letter%. I am trying to replace all of them with a single, generic trigger function.

We worked out a solution with dynamic SQL under my previous question with a simplified function. Can the same technique be extended to this more complex scenario?

EXECUTE format($$...

Best Answer

Yes, this should work (untested):

CREATE OR REPLACE FUNCTION trfn_tbl_log_timetypespan()  -- generic name
  RETURNS trigger AS
$func$
DECLARE
   _timetype varchar;
   _timetypespan_resume interval;
   _ct int;
BEGIN

CASE NEW.timetype
WHEN 'lap' THEN
   EXECUTE format($$
      SELECT timetype, timetypespan, age($1, timestmp)
      FROM   %s
      WHERE  fnname = $2
      AND    timetype IN ('start', 'resume')
      ORDER  BY stmtserial DESC
      LIMIT  1$$
    , TG_RELID::regclass)
   USING NEW.timestmp, NEW.fnname
   INTO  _timetype, _timetypespan_resume, NEW.timetypespan;

   CASE _timetype
   WHEN 'start' THEN  -- do nothing

   WHEN 'resume' THEN
      EXECUTE format($$
         SELECT age($1, timestmp) - _timetypespan_resume
         FROM   %s
         WHERE  fnname = $2
         AND    timetype = 'start'
         ORDER  BY stmtserial DESC
         LIMIT  1$$
       , TG_RELID::regclass)
      USING NEW.timestmp, NEW.fnname
      INTO  NEW.timetypespan;

   ELSE
      RAISE EXCEPTION 'There is no previous row.';
   END CASE;

WHEN 'resume' THEN
   EXECUTE format($$
      SELECT age($1, timestmp)
      FROM   %s
      WHERE  fnname = $2
      AND    timetype IN ('start', 'lap')
      ORDER  BY stmtserial DESC LIMIT 1$$
    , TG_RELID::regclass)
   USING NEW.timestmp, NEW.fnname
   INTO  NEW.timetypespan;

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN  -- do nothing
   ELSE
      RAISE EXCEPTION 'There is no previous row.';
   END IF;

WHEN 'total' THEN
   EXECUTE format($$
      SELECT COALESCE(SUM(timetypespan), $1)
      FROM (
         SELECT floor(timeidx)::int, max(timetypespan) AS timetypespan
         FROM   %1$s
         WHERE  fnname = $2
         AND    timetype = 'lap'
         AND    stmtserial > coalesce(
                 (SELECT stmtserial
                  FROM   %1$s
                  WHERE  fnname = $2
                  AND    timetype = 'total'
                  ORDER  BY stmtserial DESC
                  LIMIT  1), 0)
         GROUP  BY 1
         ) sub$$
    , TG_RELID::regclass)
   USING NEW.timetypespan, NEW.fnname
   INTO  NEW.timetypespan;

   GET DIAGNOSTICS _ct = ROW_COUNT;

   IF _ct > 0 THEN  -- do nothing
   ELSE
      RAISE EXCEPTION 'There is no previous row.';
   END IF;
END CASE;

RETURN NEW;

END
$func$  LANGUAGE plpgsql;

This is a follow-up to my previous answer to your previous question. Find explanation there:

While being at it, I simplified a couple of things. Like: the second SELECT in your first ELSIF branch was just a repetition of the SELECT one level above. I merged that for no cost.

I also removed a couple of unnecessary intermediary steps and assigned to fields of NEW directly where applicable. That's why I could remove most of your variables.

Aside: If timeidx only has positive numbers you can use the cheaper trunc(timeidx) instead of floor(timeidx).

To understand the dynamic part more easily ...

If you would implement the same just for tbl_log_a - i.e., the effectively executed code for tbl_log_a (after applying format() and EXECUTE) looks like this:

...

CASE NEW.timetype
WHEN 'lap' THEN
   SELECT timetype, timetypespan, age(NEW.timestmp, timestmp)
   FROM   tbl_log_a
   WHERE  fnname = NEW.fnname
   AND    timetype IN ('start', 'resume')
   ORDER  BY stmtserial DESC
   LIMIT  1
   INTO  _timetype, _timetypespan_resume, NEW.timetypespan;

   CASE _timetype
   WHEN 'start' THEN  -- do nothing

   WHEN 'resume' THEN
      SELECT age(NEW.timestmp, timestmp) - _timetypespan_resume
      FROM   tbl_log_a
      WHERE  fnname = NEW.fnname
      AND    timetype = 'start'
      ORDER  BY stmtserial DESC
      LIMIT  1
      INTO  NEW.timetypespan;

...
Related Question