How to find the number of rows deleted

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

How to find the number of rows deleted

Andrus Moor-2
I ran DELETE command from my ODBC client application.
I want to get the number of rows deleted by this DELETE command.

I read the DELETE command docs but havent found any function.

Any idea ?

Andrus.



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: How to find the number of rows deleted

Ropel
At least, you can do a "select count(*) from ..." just before the
delete, better if inside a transaction, if the query itself is not too
much expensive

Andrus wrote:

>I ran DELETE command from my ODBC client application.
>I want to get the number of rows deleted by this DELETE command.
>
>I read the DELETE command docs but havent found any function.
>
>Any idea ?
>
>Andrus.
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>
>  
>



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: How to find the number of rows deleted

Andrus Moor-2
Ropel, thank you.

I'm looking for a better solution because

1. Some deletes take big amount of time. This increases run time a LOT

2. This requires the use of transaction if somebody changes data between
SELECT COUNT(*) and DELETE commands.
When transaction isolation level I must set for this ? Can I use default
isolation level?

Andrus.

> At least, you can do a "select count(*) from ..." just before the delete,
> better if inside a transaction, if the query itself is not too much
> expensive
>
>>I ran DELETE command from my ODBC client application.
>>I want to get the number of rows deleted by this DELETE command.
>>
>>I read the DELETE command docs but havent found any function.
>>
>>Any idea ?
>>
>>Andrus.
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 3: Have you checked our extensive FAQ?
>>
>>               http://www.postgresql.org/docs/faq
>>
>>
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>



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

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: How to find the number of rows deleted

Tom Lane-2
In reply to this post by Andrus Moor-2
"Andrus" <[hidden email]> writes:
> I ran DELETE command from my ODBC client application.
> I want to get the number of rows deleted by this DELETE command.

I'm sure ODBC provides a way to do that, but you're asking the wrong
list about what it is ... try pgsql-odbc.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: How to find the number of rows deleted

John DeSoi
In reply to this post by Andrus Moor-2

On Jul 18, 2005, at 7:00 AM, Andrus wrote:

> I ran DELETE command from my ODBC client application.
> I want to get the number of rows deleted by this DELETE command.
>
> I read the DELETE command docs but havent found any function.
>
> Any idea ?

I don't use ODBC, but you should get that directly back from the  
DELETE command. Is there not a way to get the command return value  
with your ODBC driver?

 From the DELETE docs:

Outputs

On successful completion, a DELETE command returns a command tag of  
the form

DELETE count
The count is the number of rows deleted. If count is 0, no rows  
matched the condition (this is not considered an error).





John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: How to find the number of rows deleted

codeWarrior-2
In reply to this post by Andrus Moor-2
After an application updates, deletes, or inserts rows, it can call
SQLRowCount to determine how many rows were affected. SQLRowCount returns
this value whether or not the rows were updated, deleted, or inserted by
executing an UPDATE, DELETE, or INSERT statement, by executing a positioned
update or delete statement, or by calling SQLSetPos.

If a batch of SQL statements is executed, the count of affected rows might
be a total count for all statements in the batch or individual counts for
each statement in the batch. For more information, see Batches of SQL
Statements and Multiple Results.

The number of affected rows is also returned in the SQL_DIAG_ROW_COUNT
diagnostic header field in the diagnostic area associated with the statement
handle. However, the data in this field is reset after every function call
on the same statement handle, whereas the value returned by SQLRowCount
remains the same until a call to SQLBulkOperations, SQLExecute,
SQLExecDirect, SQLPrepare, or SQLSetPos.

And Tom is right....



"Andrus" <[hidden email]> wrote in message
news:dbg24r$27d4$[hidden email]...
>I ran DELETE command from my ODBC client application.
> I want to get the number of rows deleted by this DELETE command.
>
> I read the DELETE command docs but havent found any function.
>
> Any idea ?
>
> Andrus.
>



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: How to find the number of rows deleted

Andrus Moor-2
My ODBC client is Microsoft Visual FoxPro

I ran delete command using its sqlexec() function like:

SQLEXEC(nConnhandle, "DELETE FROM mytable")

sqlexec()  function returns the result from ordinary select table in a
cursor  SQLRESULT

Unfortunately, no result is returned if DELETE command is executed.
There is no SQLRowCount function in FoxPro.

If Microsoft SQL server returns the number of rows from last command in a
variable something like @@RowCount.

I'm expecting that Postgres has connection level function like currval()
which returns the result of rows affected by last command. So I can execute

SELECT rowcount();

in Postgres.

I can retrieve the connection handle value but I don?t know hot to get the
row count from it.

Any idea ?

Andrus.

"Greg Patnude" <[hidden email]> wrote in message
news:dbgfg9$213i$[hidden email]...

> After an application updates, deletes, or inserts rows, it can call
> SQLRowCount to determine how many rows were affected. SQLRowCount returns
> this value whether or not the rows were updated, deleted, or inserted by
> executing an UPDATE, DELETE, or INSERT statement, by executing a
> positioned update or delete statement, or by calling SQLSetPos.
>
> If a batch of SQL statements is executed, the count of affected rows might
> be a total count for all statements in the batch or individual counts for
> each statement in the batch. For more information, see Batches of SQL
> Statements and Multiple Results.
>
> The number of affected rows is also returned in the SQL_DIAG_ROW_COUNT
> diagnostic header field in the diagnostic area associated with the
> statement handle. However, the data in this field is reset after every
> function call on the same statement handle, whereas the value returned by
> SQLRowCount remains the same until a call to SQLBulkOperations,
> SQLExecute, SQLExecDirect, SQLPrepare, or SQLSetPos.
>
> And Tom is right....
>
>
>
> "Andrus" <[hidden email]> wrote in message
> news:dbg24r$27d4$[hidden email]...
>>I ran DELETE command from my ODBC client application.
>> I want to get the number of rows deleted by this DELETE command.
>>
>> I read the DELETE command docs but havent found any function.
>>
>> Any idea ?
>>
>> Andrus.
>>
>
>



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: How to find the number of rows deleted

Andrus Moor-2
In reply to this post by Tom Lane-2
>> I ran DELETE command from my ODBC client application.
>> I want to get the number of rows deleted by this DELETE command.
>
> I'm sure ODBC provides a way to do that, but you're asking the wrong
> list about what it is ... try pgsql-odbc.

Tom,

pgsql.odbc newsgroup has only 5 messages in the whole this year. None of
them has got any replies.

I posted this question also pgsql.interfaces.odbc newgroup some days ago but
havent got
any reply.

pgsql.interfaces.odbc has lower traffic. 50% on messages get replies.

pgsql.general has much higher traffic. 80% of messages get replies.

Andrus.



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: How to find the number of rows deleted

Scott Marlowe
On Mon, 2005-07-18 at 11:53, Andrus wrote:

> >> I ran DELETE command from my ODBC client application.
> >> I want to get the number of rows deleted by this DELETE command.
> >
> > I'm sure ODBC provides a way to do that, but you're asking the wrong
> > list about what it is ... try pgsql-odbc.
>
> Tom,
>
> pgsql.odbc newsgroup has only 5 messages in the whole this year. None of
> them has got any replies.
>
> I posted this question also pgsql.interfaces.odbc newgroup some days ago but
> havent got
> any reply.
>
> pgsql.interfaces.odbc has lower traffic. 50% on messages get replies.
>
> pgsql.general has much higher traffic. 80% of messages get replies.

I think you're making the classic mistake of equating the usenet news
versions of a list with the actual mailing list.

Take a look here:

http://archives.postgresql.org/pgsql-odbc/2005-07/threads.php

That's the list for just July, and there's easily 40 or more messages
there.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: How to find the number of rows deleted

Paul Thomas-3
In reply to this post by Andrus Moor-2

On 18/07/2005 17:47 Andrus wrote:
> Unfortunately, no result is returned if DELETE command is executed.
> There is no SQLRowCount function in FoxPro.

That's probably a feature of the language and will be so regardless of the
database used. I expect it wraps the ODBC APIs up in a more managable form.

Paul Thomas
+------------------------------+-------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for Business           |
| Computer Consultants         | http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+-------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: How to find the number of rows deleted

Andrus Moor-2
In reply to this post by Scott Marlowe
>> pgsql.odbc newsgroup has only 5 messages in the whole this year. None of
>> them has got any replies.

> I think you're making the classic mistake of equating the usenet news
> versions of a list with the actual mailing list.
>
> Take a look here:
>
> http://archives.postgresql.org/pgsql-odbc/2005-07/threads.php
>
> That's the list for just July, and there's easily 40 or more messages
> there.

I'm using news.postgresql.org  news server
Those messages are totally different from pgsql.odbc newsgroup in
news.postgresql.org  !

Is it possible to use psql-odbc usenet news via news server  ?

Are there also messages missing from pgsql.general newsgroup from
news.postgresql.org  ?

How I can read and reply to all messages without receiving them all to my
e-mail mailbox ?

Andrus.



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: How to find the number of rows deleted

Andrus Moor-2
In reply to this post by Paul Thomas-3
>> Unfortunately, no result is returned if DELETE command is executed.
>> There is no SQLRowCount function in FoxPro.
>
> That's probably a feature of the language and will be so regardless of the
> database used. I expect it wraps the ODBC APIs up in a more managable
> form.

Paul,

If Microsoft SQL server I can probably the number of deleted rows using @@
variable.
So feature not getting number of rows from FoxPro is unique to Postgres in
my case.
FoxPro allows me use use odbc handle directly but I'm not sure it can help.

Is it possible to get the number of deleted rows in pg/SQL ?
How to create stored procedure which wraps delete command and returns number
of deleted rows as table column?

So I instead of delete I can use

SELECT delete_return_deleted_rows('DELETE FROM mytable WHERE mycondition');


Andrus.





---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: How to find the number of rows deleted

Guy Rouillier
In reply to this post by Andrus Moor-2
Andrus wrote:

>
> How I can read and reply to all messages without receiving them all
> to my e-mail mailbox ?

From the Mailing Lists page of the PostgreSQL site
http://www.postgresql.org/community/lists/:

"The mailing lists are also available at the PostgreSQL news server.
However, in order to post to a list, you must still subscribe to that
list (but it is possible to subscribe without receiving mail)."

Update your subscriptions to request that no emails be sent.  Then read
posts and reply through the PG news serve.

--
Guy Rouillier


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq