INSERT a real number in a column based on other columns OLD INSERTs

Question

In PostgreSQL I have this table... (there is a primary key serial column in the most left side "stmtserial" which is not shown in this image)

in the table above, all columns are entered via queries, except the "time_index" which is automatically filled via a BEFORE INSERT, PER-ROW trigger.

This is the code to create the same table (without any value) so everyone could create it using the Postgre SQL query panel.

    CREATE TABLE table_ebscb_spa_log04
(
  pcnum smallint,
  stmtserial integer NOT NULL DEFAULT nextval('table_ebscb_spa_log04_stmtnum_seq'::regclass),
  fn_name character varying,
  "time" timestamp without time zone,
  time_elapse character varying,
  time_type character varying,
  time_index real,
  CONSTRAINT table_ebscb_spa_log04_pkey PRIMARY KEY (stmtserial)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE table_ebscb_spa_log04
  OWNER TO postgres;

I've almost done the first n second part of the trigger, but I'm wondering if I'm taking the right approach before getting to the third part.

The first part of the trigger produces every red highlighted square in the table image, in other words it does this...

INSERT a number in the time_index column based on the INSERTed values of the fn_name and time_type columns in each row.

If both (fn_name and time_type) do a combination (eg. Check Mails - Start) that doesn't exist in any row before (above), then INSERT 1 in the time_index column.

Elif both (fn_name and time_type) do a combination that does exist in some row before (above), then INSERT the number following the one in the previous(above) match, in the time_index column.

The second part of the trigger produces every green highlighted square in the table image, in other words it does this...

INSERT a number in the "time_index" column based on the INSERTed values of the "fn_name" and "time_type" columns in each row.

If 'Lap' is INSERTed in the time_type column, then automatically fill the time_index of the same row, with the same number as the previous(above) time_index cell WHERE time_type = 'Start' and fn_name = to the one in the row where 'Lap' was INSERTed; followed by a dot; and followed by the number that follows the decimal one in the previous time_index cell WHERE time_type and fn_name = to the ones in the row where 'Lap' was INSERTed, that are not before(above) any row WHERE time_type = 'Start' and fn_name = to the one in the row where 'Lap' was INSERTed. If there isn't anyone, then start counting from 1 (so it would be 0.1).

So, this is what I have made so far... (PLEASE NOTE THE MISTAKE I DID AFTER THE ELSE IF)

CREATE OR REPLACE FUNCTION timelog()
  RETURNS trigger AS
$BODY$
DECLARE
t_ix real;
n int;

BEGIN
IF NEW.time_type = 'Start' THEN
    SELECT t.time_index FROM table_ebscb_spa_log04 t WHERE t.fn_name = NEW.fn_name AND t.time_type = 'Start' ORDER BY t.stmtserial DESC LIMIT 1 INTO t_ix;
      GET DIAGNOSTICS n = ROW_COUNT;
        IF (n = 0) THEN 
        t_ix := 1; --this is ok, here it's really necessary to set it to 1
        ELSE
        t_ix := t_ix + 1;
        END IF;

ELSE 
    IF NEW.time_type = 'Lap' THEN 
        SELECT t.time_index  FROM table_ebscb_spa_log04 t WHERE t.fn_name = NEW.fn_name AND (t.time_type = 'Start' OR time_type = 'Lap') ORDER BY t.stmtserial DESC LIMIT 1 INTO t_ix;
          GET DIAGNOSTICS n = ROW_COUNT;
            IF (n = 0) THEN 
            t_ix := 1; --!!!HERE I MADE A MISTAKE (SORRY) IT SHOULDN'T BE SET TO 1, IT SHOULD THROW AN ERROR MESSAGE 'There isn't any previous same fn_name with time_type Start';
            ELSE 
            t_ix := t_ix + 0.1;
            END IF;
  END IF;
END IF;
NEW.time_index = t_ix;
return NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION timelog()
  OWNER TO postgres;

This is the code to create the trigger:

CREATE TRIGGER timelog
    BEFORE INSERT ON table_ebscb_spa_log04
    FOR EACH ROW
    EXECUTE PROCEDURE timelog();

Now, the third part of the trigger produces the blue highlighted square in the table image, but also affects the way the green highlighted squere operate, in other words it does this...

INSERT a number in the "time_index" column based on the INSERTed values of the "fn_name" and "time_type" columns in each row. If Break is INSERTed in the time_type column, then automatically fill the time_index cell of the same row, with the same number as the previous(above) time_index cell WHERE time_type = Start and fn_name = to the one in the row where 'Break' was INSERTed; followed by a dot; and followed by the number that follows the decimal one in the previous time_index cell WHERE time_type and fn_name = to the ones in the row where 'Break' was INSERTed, that are not before(above) any cell WHERE time_type = 'Start' and 'fn_name' = to the one in the row where 'Break' was INSERTed. If there isn't anyone, then start counting from 1.

Hope some good PostgreSQL fellow programmer could give me a hand. I have read many of postgres documentation chapters, with no clue.

Thanks Advanced.

Answer 1

Based on the first two requirements, there is nothing wrong with your trigger per se, but you can greatly simplify it:

CREATE OR REPLACE FUNCTION timelog() RETURNS trigger AS $BODY$
DECLARE
  t_ix real;
BEGIN
  -- First check if you need to change NEW at all
  IF (NEW.time_type = 'Start') OR (NEW.time_type = 'Lap') THEN
    -- Now perform the expensive lookup for either of 'Start' or 'Lap'
    SELECT time_index INTO t_ix
    FROM table_ebscb_spa_log04
    WHERE fn_name = NEW.fn_name
      AND (time_type = 'Start' OR time_type = 'Lap')
    ORDER BY stmtserial DESC LIMIT 1;

    IF NOT FOUND THEN
      -- Nothing found, so NEW.time_index := 1
      NEW.time_index := 1; 
    ELSIF NEW.time_type = 'Start' THEN 
      -- Start new index for fn_name, discard any fractional part, then increment
      NEW.time_index := floor(t_ix) + 1; 
    ELSE
      -- Continue the lap, increment NEW.time_index
      NEW.time_index := t_ix + 0.1; 
    END IF;
  END IF;
  RETURN NEW;
END; $BODY$ LANGUAGE plpgsql;

There is a much easier way, however, and that will also accommodate the third requirement without a problem. Rather than looking at the "time_index" values, you should look at the "time" value, because that is what "time_index" is based upon:

CREATE OR REPLACE FUNCTION timelog() RETURNS trigger AS $BODY$
DECLARE
  t_ix real;
BEGIN
  -- Find the most recent entry for the same "fn_name" as the new record
  SELECT time_index INTO t_ix
  FROM table_ebscb_spa_log04
  WHERE fn_name = NEW.fn_name
  ORDER BY time DESC LIMIT 1;

  -- Nothing found, so NEW.time_index := 1
  IF NOT FOUND THEN
    NEW.time_index := 1;
    RETURN NEW;
  END IF;

  -- Some record exists, so update "time_index" based on previous record
  CASE NEW.time_type 
    WHEN 'Start' THEN 
      -- Start new index for fn_name, discard any fractional part, then increment
      NEW.time_index := floor(t_ix) + 1; 
    WHEN 'Lap' THEN
      -- Continue the lap, increment NEW.time_index
      NEW.time_index := t_ix + 0.1; 
    ELSE
      -- Break, find previous break or start, increment by 0.1
      SELECT time_index + 0.1 INTO NEW.time_index
      FROM table_ebscb_spa_log04
      WHERE fn_name = NEW.fn_name
        AND (time_type = 'Start' OR time_type = 'Break')
      ORDER BY time DESC LIMIT 1;
  END CASE;

  RETURN NEW;
END; $BODY$ LANGUAGE plpgsql;

This implements your logic but do note that there are some potential pitfalls:

• What if you insert a 'Lap' or a 'Break' before a 'Start'?
• What if you have more than 9 "fn_name" events after a 'Start' ("time_index" fractional part will roll over to the next integer)?

You could of course forget about the "time_index" field and the trigger altogether and generate it on the fly in a view, if your data model allows it (same with "time_elapse").