Thread: How to find the number of rows deleted
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.
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 > > > >
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 >
"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... >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. >
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... > 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... >>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. >> > >
>> 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 | +------------------------------+-------------------------------------------+
>> 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.
>> 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.
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