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