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

Hello

I just made a short test with the code provided. As Bill mentioned the
moment when the trigger is fired is essential.
I made a test with both before (worked) and after (did not work because
the row was already inserted and the returned new row is ignored).

The assignment (= or :=) does not seem to play a role, but the correct
version is as mentioned :=

Bye
Charles

On 6/20/2015 21:37, Bill Moran wrote:
> On Sat, 20 Jun 2015 10:44:21 -0700 (MST)
> litu16 <litumelendez@gmail.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.
>