Обсуждение: 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

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

Maybe you can use the new "returning" syntax to your query can get to count of rows affected.

Regards,

Richard Broersma Jr.

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

On Tue, Dec 19, 2006 at 12:54:30 -0600,
  Chandra Sekhar Surapaneni <chandu@positivenetworks.net> wrote:
> 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.

I don't think this is guaranteed to work under concurrent updates unless
you are running in serializable mode and can retry after failed updates.

There are also some things you can do to reduce contention if these updates
are happening very frequently. There should be some suggestions in the
archives from a year or two ago.

>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly