PostgreSQL trigger function using "case when" and "else"-CodePudding

I made a code for update trigger. Whenever I update, I want to fill "price" column of table "new_book" with 1000 if "price" is blank, or 5% discount if "price" is not blank. My postsql code is

create function test()
returns trigger
as
$$
declare pr1 numeric ;
begin
case when old.price is null then pr1=1000 
else pr1=pr1*0.95 end
end;
return new;
end
$$
language plpgsql;

but if i run this code, PGAdmin shows an error like that

ERROR:  syntax error at or near "else"
LINE 8: else pr1=pr1*0.95 end
        ^
SQL state: 42601
Character: 117

How can I solve this problem?

CodePudding user response：

You want an IF statement. And if you want to change the value that is stored in the table, you need to modify the new record:

create function test()
  returns trigger
as
$$
begin
  if new.price is null then 
    new.new_book := 1000;
  else 
    new.new_book := new.price * 0.95
  end if;
  return new;
end
$$
language plpgsql;

In order for the assignment to actually be persisted, you have to use a BEFORE trigger:

create trigger set_new_book_trigger
  before insert or update on the_table
  for each row 
  execute procedure test();