Skip site navigation (1) Skip section navigation (2)

Stamping rows...

From: "Di Croce, Tony" <tdicroce(at)pelco(dot)com>
To: "'pgsql-admin(at)postgresql(dot)org'" <pgsql-admin(at)postgresql(dot)org>
Subject: Stamping rows...
Date: 2004-11-19 00:23:50
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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
Any ideas?


pgsql-admin by date

Next:From: Nicholas WalkerDate: 2004-11-20 05:58:56
Subject: Escape Quoting
Previous:From: Lane RollinsDate: 2004-11-18 19:35:38
Subject: How to repair 'could not access status of transaction '

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group