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

Re: Getting a count from an update

From: "Chandra Sekhar Surapaneni" <chandu(at)positivenetworks(dot)net>
To: "Brian Hurt" <bhurt(at)janestcapital(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: Getting a count from an update
Date: 2006-12-19 18:54:30
Message-ID: 0F7F9A82BB0DBB4396A9F8386D0E061201C3343D@pos-exch1.corp.positivenetworks.net (view raw or flat)
Thread:
Lists: pgsql-novice
You can use a trigger to update a different table with the count of
number of rows updated. 
If you use the following sql, then every time you update a table called
tablename, the count column in the tabletocountrows will be incremented
for that tablename. 

create table tabletocountrows (tablename varchar, count int);

insert into tabletocountrows values ('tablename', 0); 
 
create or replace function countUpdatedRows() returns trigger as
'
Begin
   update tabletocountrows
      set count = count+1 where tablename = TG_RELNAME;
   Return NULL;
End;
' Language plpgsql;

create trigger countRows after update on tablename for each row execute
procedure countUpdatedRows();

Regards,
Chandra Sekhar Surapaneni


-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of Brian Hurt
Sent: Thursday, December 14, 2006 1:57 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] Getting a count from an update

Newbie question here: I want to be able to capture the count of the 
number of rows updated from an update command within SQL.  I know that 
psql prints out the result, I want to grab it within a function.  The 
update is a simple "UPDATE tablename SET col = value WHERE othercol = 
otherval;", which will generally update many dozens or hundreds of 
rows.  Currently I'm doing a select before doing the update, but since 
I'm doing a select count(*) I can't add a 'for update' to the end and 
lock the rows.  Is there a better way to do this?

Brian


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Responses

pgsql-novice by date

Next:From: Russell DenneyDate: 2006-12-19 20:41:16
Subject: CREATE ROLE
Previous:From: Andreas KretschmerDate: 2006-12-19 16:37:45
Subject: Re: print statements?

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