Re: Getting number of affected rows after DELETE FROM

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

> On 2010-12-17, Raimon Fernandez <coder(at)montx(dot)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 many rows 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 if the 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 get those 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't make 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't delete 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 has some 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'm not 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 profressional databases like postgreSQL, are lazy, first I have to demonstrate that they are doing something wrong in their plugin 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.