Обсуждение: getting the number of rows affected by a query

I'm trying to do some periodic updates from another DB and would like to
know the # of updates/inserts/deletes from that job.

I usually write a function which gets/uses the GETS DIAGNOSTIC ROW COUNT
parameter which will tell me how many rows were affected by the query.

Now, for this case, I'm not writing a function but merely using a normal
SQL eg:

BEGIN;

DELETE FROM foo where (x) = (select x from foobar);

INSERT INTO foo select * from foobar;

-- then I would like to update a log_table
-- eg: insert into log(proc,tablname,ins_row,delete_rows)
--      values ('update','foo',XXX,YYY)

COMMIT;

Is there a way to do this?

On Fri, 2007-11-16 at 09:00 +0800, Ow Mun Heng wrote:
> I'm trying to do some periodic updates from another DB and would like to
> know the # of updates/inserts/deletes from that job.
>
> I usually write a function which gets/uses the GETS DIAGNOSTIC ROW COUNT
> parameter which will tell me how many rows were affected by the query.
>
> Now, for this case, I'm not writing a function but merely using a normal
> SQL eg:
>
> BEGIN;
>
> DELETE FROM foo where (x) = (select x from foobar);
>
> INSERT INTO foo select * from foobar;
>
> -- then I would like to update a log_table
> -- eg: insert into log(proc,tablname,ins_row,delete_rows)
> --      values ('update','foo',XXX,YYY)
>
> COMMIT;
>
> Is there a way to do this?

Hmm.. no response..
and I've yet to be able to find out how to get this done.
Would really appreciate some help..

On Fri, Nov 16, 2007 at 09:00:46AM +0800, Ow Mun Heng wrote:
> I usually write a function which gets/uses the GETS DIAGNOSTIC ROW COUNT
> parameter which will tell me how many rows were affected by the query.
>
> Now, for this case, I'm not writing a function but merely using a normal
> SQL eg:

The server provides the number of changed rows in its response. Like
DELETE 2030. So check whatever you're using to run the commands.

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

On Fri, 2007-11-16 at 10:22 +0100, Martijn van Oosterhout wrote:
> On Fri, Nov 16, 2007 at 09:00:46AM +0800, Ow Mun Heng wrote:
> > I usually write a function which gets/uses the GETS DIAGNOSTIC ROW COUNT
> > parameter which will tell me how many rows were affected by the query.
> >
> > Now, for this case, I'm not writing a function but merely using a normal
> > SQL eg:
>
> The server provides the number of changed rows in its response. Like
> DELETE 2030. So check whatever you're using to run the commands.
>

The question is on how to use this number to be inserted into a table
instead of just being informational.

On Fri, Nov 16, 2007 at 09:00:46AM +0800, Ow Mun Heng wrote:
> I'm trying to do some periodic updates from another DB and would like to
> know the # of updates/inserts/deletes from that job.

Humm; it would be nice if you could use the new RETURNING construct
that's been introduced in 8.2, i.e. something like:

  SELECT COUNT(*) FROM (
    DELETE FROM foo RETURNING 1) x;

However PG doesn't seem to support this.  It seems logical to support
this construct now that RETURNING has been incorporated.  There's
probably something obvious that I'm missing here though.


  Sam

On 16/11/2007 10:02, Sam Mason wrote:

>   SELECT COUNT(*) FROM (
>     DELETE FROM foo RETURNING 1) x;

I haven't played with this yet, but AFAICS this will simply return the
integer value "1".

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

On Fri, Nov 16, 2007 at 12:51:07PM +0000, Raymond O'Donnell wrote:
> On 16/11/2007 10:02, Sam Mason wrote:
>
> >  SELECT COUNT(*) FROM (
> >    DELETE FROM foo RETURNING 1) x;
>
> I haven't played with this yet, but AFAICS this will simply return the
> integer value "1".

I currently get a syntax error, hence the way I wrote my message.

I'd not expect it to return 1 though.  The "1" is there simply to be
easy to evaluate, maybe "*" would have been better.  This 1 would get
returned to the outer query, which would end up counting the number of
rows deleted.


  Sam

On 16/11/2007, Sam Mason <sam@samason.me.uk> wrote:
> On Fri, Nov 16, 2007 at 12:51:07PM +0000, Raymond O'Donnell wrote:
> > On 16/11/2007 10:02, Sam Mason wrote:
> >
> > >  SELECT COUNT(*) FROM (
> > >    DELETE FROM foo RETURNING 1) x;
> >
> > I haven't played with this yet, but AFAICS this will simply return the
> > integer value "1".
>
> I currently get a syntax error, hence the way I wrote my message.
>
> I'd not expect it to return 1 though.  The "1" is there simply to be
> easy to evaluate, maybe "*" would have been better.  This 1 would get
> returned to the outer query, which would end up counting the number of
> rows deleted.
>

Using RETRNING clause in subselects are not supported yet. Look to ToDo.

Regards
Pavel Stehule

On Nov 16, 2007, at 3:26 AM, Ow Mun Heng wrote:

>
> On Fri, 2007-11-16 at 10:22 +0100, Martijn van Oosterhout wrote:
>> On Fri, Nov 16, 2007 at 09:00:46AM +0800, Ow Mun Heng wrote:
>>> I usually write a function which gets/uses the GETS DIAGNOSTIC
>>> ROW COUNT
>>> parameter which will tell me how many rows were affected by the
>>> query.
>>>
>>> Now, for this case, I'm not writing a function but merely using a
>>> normal
>>> SQL eg:
>>
>> The server provides the number of changed rows in its response. Like
>> DELETE 2030. So check whatever you're using to run the commands.
>>
>
> The question is on how to use this number to be inserted into a table
> instead of just being informational.

The specifics depend on what language you're using for your database
access.  Regardless, though, save the server's response in a variable
and use that.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On Fri, 2007-11-16 at 09:28 -0600, Erik Jones wrote:
> On Nov 16, 2007, at 3:26 AM, Ow Mun Heng wrote:
>
> >
> > On Fri, 2007-11-16 at 10:22 +0100, Martijn van Oosterhout wrote:
> >> On Fri, Nov 16, 2007 at 09:00:46AM +0800, Ow Mun Heng wrote:
> >>> I usually write a function which gets/uses the GETS DIAGNOSTIC
> >>> ROW COUNT
> >>> parameter which will tell me how many rows were affected by the
> >>> query.
> >>>
> >>> Now, for this case, I'm not writing a function but merely using a
> >>> normal
> >>> SQL eg:
> >>
> >> The server provides the number of changed rows in its response. Like
> >> DELETE 2030. So check whatever you're using to run the commands.
> >>
> >
> > The question is on how to use this number to be inserted into a table
> > instead of just being informational.
>
> The specifics depend on what language you're using for your database
> access.  Regardless, though, save the server's response in a variable
> and use that.


Turns out this is a 2 part question, for which I have 1 solved.

1. using perl DBI to pull from MSSQL to PG..
--> I found out I can use
my $ins_rows = $dbh_pg->do($query2) or die "prepare failed
$DBI::errstr";

2. using pure SQL (via pgagent jobs) to pull. This is the one which I've
yet to be able to solve w/o writing a function and using GET DIAGNOSTICS
ROW COUNT.
--> Is one able to use variables in pure SQL ? (eg: undel psql?)

Thanks

Ow Mun Heng wrote:
> Turns out this is a 2 part question, for which I have 1 solved.
>
> 1. using perl DBI to pull from MSSQL to PG..
> --> I found out I can use
> my $ins_rows = $dbh_pg->do($query2) or die "prepare failed
> $DBI::errstr";
>
> 2. using pure SQL (via pgagent jobs) to pull. This is the one which I've
> yet to be able to solve w/o writing a function and using GET DIAGNOSTICS
> ROW COUNT.
> --> Is one able to use variables in pure SQL ? (eg: undel psql?)
>

You could use PL/Perl's $_SHARED construct:

CREATE OR REPLACE FUNCTION set_id(name text, val INT4)
RETURNS text AS $$
   if ($_SHARED{$_[0]} = $_[1])
   {
     return 'ok';
   }
   else
   {
     return "can't set shared variable $_[0] to $_[1]";
   }
$$ LANGUAGE plperl;


CREATE OR REPLACE FUNCTION get_id(name text)
RETURNS INT4 IMMUTABLE AS $$
   return $_SHARED{$_[0]};
$$ LANGUAGE plperl;


I use it occasionally when i need to save some insert ID for something.
You could do the same thing with your row count.

SELECT set_id('the_row_count', CAST(currval('x') AS INT))

SELECT get_id('the_row_count') AS the_row_count;

or:

SELECT CAST(get_id('the_row_count') AS INT) AS the_row_count;

Where 'x' represents your row count, however you get that.

If you're using pg >= 8.2 there's a RETURNING clause for DELETE. I'm not
sure if that's what you want.

brian