Обсуждение: 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.

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

Thanks to everybody
I already solved,
the correct syntax was...
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;

Thanks for all again.



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

On 25/06/15 16:06, litu16 wrote:
> 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;
Please format your SQL, as it makes it easier to read!
But, you don't have to follow my conventions, though.  :-)

By formatting it, I noticed an apparent mistake (in this case,
functionally the same).
You had time_type = 'Lap' rather than t.time_type = 'Lap'.

SELECT
     t.time_index
FROM
     table_ebscb_spa_log04 t
WHERE
     t.fn_name = NEW.fn_name
     AND
     (
         t.time_type = 'Start'
         OR
         t.time_type = 'Lap'
     )
ORDER BY
     t.stmtserial DESC
LIMIT 1
INTO t_ix;