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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

  From Date Subject
Next Message Russell Denney 2006-12-19 20:41:16 CREATE ROLE
Previous Message Andreas Kretschmer 2006-12-19 16:37:45 Re: print statements?