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

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)

<http://postgresql.nabble.com/file/n5854916/screenshot.jpg>

in the table above, all columns are entered via querrys, except the
"time_index" which I is automatically filled via a 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 already made the first part of the trigger, but Im having trouble doing
the second part.

The first part of the trigger 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
before(above) in the "time_index" column.

(pls look at the example table image, this first part of the trigger will
produce every red highlighted square on it)

So, what I would like the second part of the trigger to do is...

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 a '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 = the same to one
in the row where 'Lap' was INSERTed. If there isn't anyone, then start
counting from 1 (0.1).

(I know it seems kinda odd in words, but pls look at the example table
image, this second part of the trigger will produce every green highlighted
square on it)

So, this is what I have made so far...

*    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;
            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 IN ('Start', 'Lap') ORDER BY
t.stmtserial DESC LIMIT 1 INTO t_ix;
              GET DIAGNOSTICS n = ROW_COUNT;
                IF (n = 0) THEN
                t_ix := 1;
                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;*

Im stuck after the ELSE IF, I don't know how to evaluate two conditions in
the same expression [t.time_type = 'Start' OR 'Lap'] I don't know what the
syntax should be, if I got to use "IN" or "=" or what else.

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

Thanks Advanced.

  [1]: http://i.stack.imgur.com/WIhEO.jpg



--
View this message in context:
http://postgresql.nabble.com/INSERT-a-real-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854916.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.