4

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();
  • 1
    I don't see an error posted here. Did you forget something? ;) – dezso Jan 18 '18 at 16:19
  • Rather than storing a version, you may find it more beneficial to store the timestamp of the last modification – Evan Carroll Jan 18 '18 at 21:06
5

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();
  • 1
    thank you, it works smoothly! those new and old are a bit confusing for me right now, but I got the point. thanks once again! – juliam Jan 19 '18 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.