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
insert into 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.514217
b | 2003-07-26 19:17:26.514217
c | 2003-07-26 19:17:26.514217
d | 2003-07-26 19:17:26.514217
x | 2003-07-26 19:17:26.514217
a | 2003-07-26 19:17:26.514217
b | 2003-07-26 19:17:26.514217
c | 2003-07-26 19:17:26.514217
d | 2003-07-26 19:17:26.514217
x | 2003-07-26 19:17:28.300914
a | 2003-07-26 19:17:26.514217
b | 2003-07-26 19:17:26.514217
c | 2003-07-26 19:17:26.514217
d | 2003-07-26 19:17:26.514217
x | 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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
