I have a PostgreSQL table phase_steps
, with the following example rows:
phase_step_id|step_type|step_status|
-------------+---------+-----------+
1| RESEARCH| |
2| SURVEY| |
Update values to the step_status
column depend on what value the
step_type
value is.
When step_type
is 'RESEARCH', only values of 'COMPLETE' or 'INCOMPLETE' can be entered for step_status
values.
When step_type
is 'SURVEY', only values of 'ASSIGNED' or 'NOT ASSIGNED' can be entered for step_status
values.
I tried to manage the 'RESEARCH' step_status
constraints with this procedure:
create or replace function insert_step_status_value() returns trigger as $$
begin
if (new.step_status != 'COMPLETE') or (new.step_status != 'INCOMPLETE')
from phase_steps where step_type = 'RESEARCH'
then
raise exception 'Status value not in range for this phase step';
end if;
return new;
end;
$$ language plpgsql;
create trigger check_step_status_value before update on phase_steps
for each row execute procedure insert_step_status_value();
However, an insert like
update jobs.phase_steps
set step_status_lu = 'INCOMPLETE'
where phase_step_id = 1;
gives an error:
SQL Error [P0001]: ERROR: Status value not in range for this phase step Where: PL/pgSQL function insert_step_status_value() line 6 at RAISE
Thoughts?
CREATE TABLE
statement showing data types and constraints).