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: B2CC0E0F2C10D511B86600B0D06898420EA053DE@NETSRVR1.pelco.org (view raw or flat)
Thread:
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
solution. 
 
Any ideas?
 
    td
 

Responses

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-2014 The PostgreSQL Global Development Group