I am trying to find a way to stamp all of the rows affected by an update (or insert) with a unique value. For example, consider the following table:
create table people( name text, address text, city text, state text, zip text, phone text, change_number integer );
I would like the "change_number" column to hold the unique value.
I would like someone to be able to submit a query like this:
update people set name='tony', address='12345 A St', city='Downey', state='Ca', zip='92372', phone='864-0618' where zip='60612';
At this point, I want a couple things to happen:
1) Generate a unique "change_number" for this query. I can probably do this with a sequence.
2) As each row is updated (with the values the user specified in the update command), set it's "change_number" to be equal to the value generated in step 1.
At first I tried to do this as a trigger function. The problem with this solution is that if I dont have the function called for each row, I cannot modify those rows. if I DO have the function called for each row, how do they get the "change_number" they cannot simply call nextval() as it will be different for each row (they also couldn't call curval() because then who is ever calling nextval()?)
I also looked into rules, and but couldn't come up with a non recursive solution.
Any ideas?
td