Thread: getting trigger to work
This function & trigger work: create function prosp_tf_bi() returns opaque as ' begin if new.pros_id is null then new.pros_id:= 1000; end if; return new; end; ' language 'plpgsql'; create trigger prosp_tr_bi before insert on prospector for each row execute procedure prosp_tf_bi(); This function & trigger don't work: create function prosp_tf_bi() returns opaque as ' begin if new.pros_id is null then new.pros_id:= A1000; end if; return new; end; ' language 'plpgsql'; create trigger prosp_tr_bi before insert on prospector for each row execute procedure prosp_tf_bi(); The error is "Attribute 'a1000' not found" The column pros_id is char(5). How do I put in the 'A1000'?
Merrill Oveson wrote: >This function & trigger don't work: > >create function prosp_tf_bi() returns opaque as ' > begin > if new.pros_id is null then > new.pros_id:= A1000; > end if; > return new; > end; ' language 'plpgsql'; > > >The error is "Attribute 'a1000' not found" > >The column pros_id is char(5). >How do I put in the 'A1000'? > Hi, You need to surround the new value with two pairs of quotation marks, like this: ''A1000'' Regards, Antonio Sergio
On Tue, 2002-03-19 at 15:46, Merrill Oveson wrote: > The error is "Attribute 'a1000' not found" > > The column pros_id is char(5). > How do I put in the 'A1000'? new.pros_id:= ''A1000''; Your first example used a number, which was automatically converted to a char for comparison. The second was interpreted as an identifier and lower-cased, but you had no attribute in the table called "a1000". You need to put it in single quotes to have it treated as a char constant; since you are inside a plpgsql program declaration, the quotes need to be doubled up. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C " And God shall wipe away all tears from their eyes; and there shall be no more death, neither sorrow, nor crying, neither shall there be any more pain: for the former things are passed away." Revelations 21:4