Very strange 'now' behaviour in nested triggers. - Mailing list pgsql-sql

In short, the idea this example is to test for is to split a
comma-separated value of some text attribute (given to the INSERT
operator) and then insert a row for each of the parts of that text
value.  I've tried to do this thru a nested triggers approach.


create
table xxx (   s text,   t timestamp       default 'now'
);

create
function xxx () returns trigger
language plpgsql
as '
declare   tail text;   head integer;
begin   tail:= substring(new.s, \'[^,]+$\');   head:= length(new.s)-          length(tail) -1;   if head > 0 then
insertinto xxx values (           substring(new.s for head)           --,new.t       );   end if;   new.s:= trim(tail);
 raise notice \'"%"\', new.s;   raise notice \'"%"\', new.t;   return new;
 
end;
';

create
trigger xxx   before insert on xxx   for each row execute procedure       xxx ();


Then:


zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE:  "a"
NOTICE:  "2003-07-26 19:17:26.514217"
NOTICE:  "b"
NOTICE:  "2003-07-26 19:17:26.514217"
NOTICE:  "c"
NOTICE:  "2003-07-26 19:17:26.514217"
NOTICE:  "d"
NOTICE:  "2003-07-26 19:17:26.514217"
NOTICE:  "x"
NOTICE:  "2003-07-26 19:17:26.514217"
INSERT 223886 1
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE:  "a"
NOTICE:  "2003-07-26 19:17:26.514217"
NOTICE:  "b"
NOTICE:  "2003-07-26 19:17:26.514217"
NOTICE:  "c"
NOTICE:  "2003-07-26 19:17:26.514217"
NOTICE:  "d"
NOTICE:  "2003-07-26 19:17:26.514217"
NOTICE:  "x"
NOTICE:  "2003-07-26 19:17:28.300914"
INSERT 223891 1
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE:  "a"
NOTICE:  "2003-07-26 19:17:26.514217"
NOTICE:  "b"
NOTICE:  "2003-07-26 19:17:26.514217"
NOTICE:  "c"
NOTICE:  "2003-07-26 19:17:26.514217"
NOTICE:  "d"
NOTICE:  "2003-07-26 19:17:26.514217"
NOTICE:  "x"
NOTICE:  "2003-07-26 19:17:30.948737"
INSERT 223896 1


zzz=> SELECT * from xxx;s |             t              
---+----------------------------a | 2003-07-26 19:17:26.514217b | 2003-07-26 19:17:26.514217c | 2003-07-26
19:17:26.514217d| 2003-07-26 19:17:26.514217x | 2003-07-26 19:17:26.514217a | 2003-07-26 19:17:26.514217b | 2003-07-26
19:17:26.514217c| 2003-07-26 19:17:26.514217d | 2003-07-26 19:17:26.514217x | 2003-07-26 19:17:28.300914a | 2003-07-26
19:17:26.514217b| 2003-07-26 19:17:26.514217c | 2003-07-26 19:17:26.514217d | 2003-07-26 19:17:26.514217x | 2003-07-26
19:17:30.948737
(15 rows)


So, all the timestamps except those for the last 'x' field are the
same!  These "the same" timestamps are really the timestamp of the
first top-level INSERT.  And the timestamps for the last field of the
comma-separated string are the correct things.  This last field is
cultivated by the top-level trigger's call.

If to set new.t for nested triggers explicitly (commented in the
trigger code above), then all will be ok.  But this is not a cure, of
course.

So, what does it mean?  Is this a bug (PostgreSQL 7.3.2)?  Or do I
misunderstand something?  

Thanks in advance.