Re: getting the number of rows affected by a query

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