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

1 Answer 1

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
  • 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, 2018 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.