Re: INSERT a number in a column based on other columns OLD INSERTs

On 06/20/2015 10:44 AM, litu16 wrote:
> In PostgreSQL I have this table... (there is a primary key in the most left
> side "timestamp02" which is not shown in this image)
>
> in the table above, all columns are entered via querrys, except the
> "time_index" which I would like to be filled automatically via a trigger
> each time each row is filled.
>
> 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_log02
> (
>    pcnum smallint,
>    timestamp02 timestamp with time zone NOT NULL DEFAULT now(),
>    fn_name character varying,
>    "time" time without time zone,
>    time_elapse character varying,
>    time_type character varying,
>    time_index real,
>    CONSTRAINT table_ebscb_spa_log02_pkey PRIMARY KEY (timestamp02)
> )
> WITH (
>    OIDS=FALSE
> );
> ALTER TABLE table_ebscb_spa_log02
>    OWNER TO postgres;*
>
> What I would like 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 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 trigger will produce every red
> highlighted square on it)
>
>
> I have tried so far this to create the function:
>
> CREATE OR REPLACE FUNCTION on_ai_myTable() RETURNS TRIGGER AS $$
> DECLARE
> t_ix real;
> n int;
>
> BEGIN
> IF NEW.time_type = 'Start' THEN
>      SELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name =
> NEW.fn_name AND t.time_type = 'Start' ORDER BY t.timestamp02 DESC LIMIT 1
> INTO t_ix;
>        GET DIAGNOSTICS n = ROW_COUNT;
>          IF (n = 0) THEN
>          t_ix = 1;
>          ELSE
>          t_ix = t_ix + 1;


You need to use the assignment operator:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT

so:

t_ix := 1

>          END IF;
> END IF;
> NEW.time_index = t_ix;

Same here.

> return NEW;
> END
> $$
> LANGUAGE plpgsql;
>
>
> But when I manually insert the values in the table, nothing change (no error
> message) time_index column just remain empty, what am I doing wrong???
>
> Please some good PostgreSQL fellow programmer could give me a hand, I really
> have come to a death point in this task, I have any more ideas.
>
>
>
> --
> View this message in context:
http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com