Re: INSERT a real number in a column based on other columns OLD INSERTs - Mailing list pgsql-general

On 06/23/2015 11:20 PM, litu16 wrote:
> 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.

Might want to reread this section:):

http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS

40.6.2.2. IF-THEN-ELSE

Examples:

IF parentid IS NULL OR parentid = ''

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


--
Adrian Klaver
adrian.klaver@aklaver.com