Re: Getting a count from an update

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@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Brian Hurt
Sent: Thursday, December 14, 2006 1:57 PM
To: pgsql-novice@postgresql.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