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

On Sat, 20 Jun 2015 10:44:21 -0700 (MST)
litu16 <litumelendez(at)gmail(dot)com> 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;
> END IF;
> END IF;
> NEW.time_index = t_ix;
> 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.

Couple things.

First off, you don't show your statement for creating the trigger. This is important.
The trigger has to be a BEFORE trigger FOR EACH ROW. Otherwise, the returned value
won't do anything. It should read like this:

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_ebscb_spa_log02
FOR EACH ROW EXECUTE PROCEDURE on_ai_myTable();

If you created it with AFTER INSERT or FOR EACH STATEMENT, then the trigger won't
work as desired.

The other thing about assignment being := was already mentioned.

--
Bill Moran