Getting number of affected row after performing update

On 19/02/10 05:42, Yan Cheng Cheok wrote:

Of course, I get an error at line :
IF @@ROWCOUNT=0 THEN

May I know what is the correct PostgreSQL syntax for @@ROWCOUNT after update?

Best place for this sort of information is the manuals:

http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

GET DIAGNOSTICS my_variable = ROWCOUNT;

--
Richard Huxton
Archonet Ltd

On 19 Feb 2010, at 6:42, Yan Cheng Cheok wrote:

-- HOW?!?!
--ERROR: column "rowcount" does not exist
--LINE 1: SELECT @@ROWCOUNT=0
IF @@ROWCOUNT=0 THEN
EXECUTE 'INSERT INTO statistic(fk_lot_id, "value", measurement_type, statistic_type) VALUES ($1, $2, $3, $4)'
USING _lotID, _value, _measurementType, _statisticType;
END IF;

May I know what is the correct PostgreSQL syntax for @@ROWCOUNT after update?

In plpgsql you can test for FOUND or NOT FOUND after performing a query:

IF NOT FOUND THEN
EXECUTE '...'
END IF;

I think that's more convenient for your case than counting actual rows.

Alban Hertroys

--
Screwing up is the best way to attach something to the ceiling.

!DSPAM:737,4b7e795110442010528220!

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,

regards,

r.

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

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

--
⚂⚃ 100% natural

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

Hello,

We have two postgreSQL servers that are in the latest 9.x as testing, but when we use pg_dump and pg_restore, our functions and triggers are never copied to postgreSQL Server 9.x.

This is how we restore:

data=`date +%Y_%m_%d`
pg_restore -c -i -h 192.168.0.9 -p 5432 -U postgres -d globalgest -v "/Users/montx/documents/BackUp/globalgest/globalgest_"$data

we have only two warnings:
...
pg_restore: dropping FUNCTION rowsaffected()
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 32; 1255 36705 FUNCTION rowsaffected() postgres
pg_restore: [archiver (db)] could not execute query: ERROR: function public.rowsaffected() does not exist
Command was: DROP FUNCTION public.rowsaffected();
pg_restore: dropping FUNCTION repairassentamentsnumero(integer)
pg_restore: dropping FUNCTION process_audit()
pg_restore: dropping FUNCTION increment_lock_version()
pg_restore: dropping FUNCTION increment(integer)
pg_restore: dropping FUNCTION getserialnumber(integer, integer)
pg_restore: dropping FUNCTION comptesrepair()
pg_restore: dropping FUNCTION rowsaffected()
pg_restore: [archiver (db)] Error from TOC entry 31; 1255 36704 FUNCTION rowsaffected() postgres
pg_restore: [archiver (db)] could not execute query: ERROR: function menus.rowsaffected() does not exist
Command was: DROP FUNCTION menus.rowsaffected();
pg_restore: dropping FUNCTION process_audit()
pg_restore: dropping PROCEDURAL LANGUAGE plpgsql
pg_restore: dropping COMMENT SCHEMA public
pg_restore: dropping SCHEMA public
pg_restore: dropping SCHEMA menus
pg_restore: dropping SCHEMA audit
pg_restore: creating SCHEMA audit
pg_restore: creating SCHEMA menus
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating PROCEDURAL LANGUAGE plpgsql
pg_restore: creating FUNCTION process_audit()
pg_restore: creating FUNCTION rowsaffected()
pg_restore: creating FUNCTION comptesrepair()
pg_restore: creating FUNCTION getserialnumber(integer, integer)
pg_restore: creating FUNCTION increment(integer)
pg_restore: creating FUNCTION increment_lock_version()
pg_restore: creating FUNCTION process_audit()
pg_restore: creating FUNCTION repairassentamentsnumero(integer)
pg_restore: creating FUNCTION rowsaffected()
pg_restore: creating FUNCTION updateallcomptes(integer)
pg_restore: creating FUNCTION updatecompte(integer)
pg_restore: creating FUNCTION updatecompte11(integer)
pg_restore: creating FUNCTION updatecompte3_5(integer)
pg_restore: creating TABLE assentaments
pg_restore: creating TABLE audit
...

when restoring the same file to any of our postgreSQL Servers 8.x we have no problems.

thanks,

r.

ok, solved. it was a problem with the application that interfaces with pg that has a bug ...

sorry,

regards,

r.

On 20dic, 2010, at 09:28 , Raimon Fernandez wrote:

On Mon, Dec 20, 2010 at 3:28 AM, Raimon Fernandez <coder@montx.com> wrote:

when restoring the same file to any of our postgreSQL Servers 8.x we have no problems.

which version of pg_dump/restore are you using? The one from 8.x or
9.0? Try using the ones from 9.0.