0

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.

2

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:

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

  • 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. – Natysiu16 Aug 26 '15 at 17:56
  • 1
    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 – Erwin Brandstetter Aug 26 '15 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.