Re: Getting number of affected rows after DELETE FROM - Mailing list pgsql-general

On 19dic, 2010, at 10:33 , Jasen Betts wrote:

> On 2010-12-17, Raimon Fernandez <coder@montx.com> wrote:
>> Hi,
>>
>> I'm trying to solve what I think must be a real trivial question.
>>
>> When I use psql after every DELETE FROM table WHERE id=xxxx I get how many rows were affected, in this case,
deleted.
>>
>> Also I've implemented the full FrontEnd/BackEnd Protocol v3 and there after a CommandComplete also I receive how
manyrows were affected. 
>>
>> But now, I'm using REALstudio www.realsoftware.com with their plugin, and I can't get the rows affected.
>>
>> I can send a simple DELETE FROM table WHERE id=xxxx  and all what I get is nothing, no rows, no set, no info, even
ifthe action didn't delete any row because the id was wrong. 
>>
>> They say that if the DELETE gives an empty string, means that PostgreSQL isn't returning nothing and that I have to
getthose values with some special values, like return parameters. 
>>
>> In pg/plsql I've used sometimes the GET DIAGNOSTICS <variable> = ROW_COUNT or FOUND with great success, but I really
can'tmake them work outside their main function. 
>>
>> There is something like select lastval(); but for rows affected ?
>>
>> thanks in advance,
>
> Easiest work-around is to add "returning true" on the end of your delete
> then the delete will return some row(s) when it succeeds.
>
>

thanks for your idea ...

I've tried to add after my DELETE FROM xxxxx the RETURNING TRUE and when it succeeds I get only a True, when postgre
can'tdelete the row because can't find it, I've get NIL. 

Just to be sure, but the RETURNING TRUE is not a mandatory option to get how many rows were affected after an
insert,delete,update? 

I'm not sure if REALstudio uses the libpq in their plugin. The libpq returns how many rows were affected or at least
hassome option to return those values ? 

Last year I made some postgreSQL for iPhone and I compiled the libpq but only for SELECTS, not inserts or delete, and
I'mnot sure of this option. 

> Best solution is to get realstudio to fix their plugin or use a different framework.

Of course, but those 'commercial frameworks' that are not really interested in doing professional front ends for
profressionaldatabases like postgreSQL, are lazy,  first I have to demonstrate that they are doing something wrong in
theirplugin or at least that they have a simple option to implement this, wich I consider, a mandatory for professional
databases.

Also I'm open to other frameworks but there are few that can deploy the same code to Windows, OS X and Linux.

Thanks again,

regards,

r.