How to find the number of rows deleted

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:

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.

"Andrus" <eetasoft@online.ee> 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

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

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" <eetasoft@online.ee> wrote in message
news:dbg24r$27d4$1@news.hub.org...

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" <gpatnude@hotmail.com> wrote in message
news:dbgfg9$213i$1@news.hub.org...

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.

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.

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

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.

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.

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