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
