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
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? |