PostgreSQL – Function to Update Column Value if Row Was Updated

postgresqltrigger

I am trying to create an function which will increment my version column value (int) if I update any column of my database row.
For instance, if i have a foo table that has columns (id, title, version) and the default version is 1, I want my vesrion to be incremented once I update a title of my single foo row. I wrote the following function but it throws an error once I test it:

create or replace function update_foo_version()
returns trigger as $body$
  begin
    update foo
    set version = version::int + 1
    where old.id=new.id;
    return new;
  end
$body$
language plpgsql;

My trigger for this function:

create trigger update_version 
after update on foo
for each row execute procedure update_foo_version();

Best Answer

It's hard to tell where you're going wrong with the error message but I'll speculate on two things. You want a BEFORE trigger, and this won't work: where old.id=new.id;

I think what you want is,

create or replace function update_foo_version()
returns trigger as $body$
  begin
    new.version = old.version + 1;
    return new;
  end
$body$
language plpgsql;

create trigger update_version 
BEFORE update on foo
for each row execute procedure update_foo_version();
Related Question