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

Question

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($$...

Answer 1

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:

• Trigger function using current row and current table name as variables

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;
...

• Natysiu16: Thanks @Erwin Brandstetter, I wil test it, thanks again.
• Natysiu16: Thanks @Erwin Brandstetter. I have one doubt though, is there some performance lost by using this "dynamically determined table name" method compared to the set method (SELECT * FROM tbl_name)? thanks advanced.
• Natysiu16: Hi @Erwin Brandstetter, I have tried replacing floor(timeidx) with trunc(timeidx) in the Trigger Function, but then it shows me an error "near trunc" when running it. I have tried also truncate? am I doing something wrogn? Thanks Advanced.
• Erwin Brandstetter: @Natysiu16: Performance depends on many factors. Details: dba.stackexchange.com/a/8189/3684. You should be able to replace floor() with trunc(). What's the data type of timeidx?
• Natysiu16: Hi @Erwin Brandstetter, the datatype of timeidx is real. Also I have tried to see why the second SELECT in my first ELSIF branch was a repetition of the SELECT one level above, you mean this right? SELECT timetypespan FROM tbl_log_a WHERE fnname = NEW.fnname AND timetype = 'resume' ORDER BY stmtserial DESC LIMIT 1 INTO v_timetypespan_resume; is selecting timetypespan, the one level above line is selecting timetype both are different columns, do you meaned that?. Thanks Advanced.