Compare postgresql enum in a function

Question

I have a function defined that I would like to have update a record value it is modified.

CREATE FUNCTION update_project_status_away_from_started()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.status = 'started' THEN
    NEW.status = 'updating';
    RETURN NEW;
  END IF;
RETURN OLD;
END;
$$ language 'plpgsql';

However when I do update a row, I get the following error:

ERROR: operator does not exist: projectstatus = character varying

The enum is defined as follow:

CREATE TYPE projectstatus AS ENUM ('started', 'updating', 'complete');

My understanding of this is that the enum is being compared to a string and doesn't know what to do. Unfortunately I don't know how to cast the string ('started') to an enum. The postgresql pages at http://www.postgresql.org/docs/9.1/static/datatype-enum.html dont really help me out much. Anyone have any idea?

Answer 1

Postgres is complaining that you need to cast that:

IF OLD.status = 'started'::projectstatus THEN
  NEW.status := 'updating'::projectstatus;
  RETURN NEW;
END IF;

Also, and as noted in Igor's answer, it's better to use := for assignment rather than the legacy (and deprecated, but still functional) = assignment operator.

Answer 2

Operator = is for comparison. Use := for assignment.

Something like:

  IF OLD.status = 'started' THEN
    NEW.status := 'updating';
    RETURN NEW;
  END IF;