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

Hi

The two things have nothing in common. With NEW.time_index = t_ix you
set the field with a value and with return you return the record (with
the modified field) to make the insert.
You have to return NEW, because you have a trigger function. The
function must return a record of the same type as the table the trigger
has been created for. You can see it as this:

You make an insert into a table that has a trigger implemented in a
trigger function.
BEFORE it is inserted, the record is passed as NEW to the trigger function.
In the function, in this case, you modify a field of NEW and return the
modified record.
Now the insert is done as usual using the NEW record returned by the
trigger function.

Bye
Charles

On 6/21/2015 03:07, litu16 wrote:
> Hi, thanks
> yes I was using AFTER, but it only works with BEFORE
> so finally I got it to work.
> thanks to all
>
> Im just still wondering
> why here...
>
> *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;*
>
> I have to put return NEW, instead of NEW.time_index = t_ix????
>
> Thanks to all again.
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/INSERT-a-number-in-a-column-based-on-other-columns-OLD-INSERTs-tp5854577p5854602.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>