SEO

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?


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFHPWFvIB7bNG8LQkwRAnkPAJ0Vjj2E8BtA4NixA7OzFq CzYxnu/QCeOFUe
JdxfQPu+bvCvfUl0F9UNUxQ=
=gNZB
-----END PGP SIGNATURE-----

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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend