Thread: Getting number of affected row after performing update

Getting number of affected row after performing update

From

Yan Cheng Cheok

Date:

19 February 2010, 04:42:32

By referring to article at :

http://blogs.msdn.com/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx

I try to implement as follow :

CREATE OR REPLACE FUNCTION update_or_insert_statistic(int, text, text, double precision)
  RETURNS void AS
$BODY$DECLARE
  _lotID ALIAS FOR $1;
  _measurementType ALIAS FOR $2;
  _statisticType ALIAS FOR $3;
  _value ALIAS FOR $4;
BEGIN
    EXECUTE 'UPDATE statistic SET value = $1 WHERE fk_lot_id = $2 AND measurement_type = $3 AND statistic_type = $4'
    USING _value, _lotID, _measurementType, _statisticType;

    -- 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;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION delete_tables(int) OWNER TO postgres;

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?

Thanks!

Thanks and Regards
Yan Cheng CHEOK

Re: Getting number of affected row after performing update

From

Richard Huxton

Date:

19 February 2010, 07:52:37

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

Re: Getting number of affected row after performing update

From

Alban Hertroys

Date:

19 February 2010, 10:43:43

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!

Getting number of affected rows after DELETE FROM

From

Raimon Fernandez

Date:

17 December 2010, 21:49:25

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
rowswere 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
theaction 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
thosevalues 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,

regards,

r.

Re: Getting number of affected rows after DELETE FROM

From

Jasen Betts

Date:

19 December 2010, 08:34:17

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

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

--
⚂⚃ 100% natural

Re: Getting number of affected rows after DELETE FROM

From

Raimon Fernandez

Date:

19 December 2010, 17:59:09

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.

pg_restore 8.x to postgreSQL 9.x functions and triggers aren't created

From

Raimon Fernandez

Date:

20 December 2010, 07:29:19

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

Re: pg_restore 8.x to postgreSQL 9.x functions and triggers aren't created [solved]

From

Raimon Fernandez

Date:

20 December 2010, 07:35:33

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:

> 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
functionsand 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.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: pg_restore 8.x to postgreSQL 9.x functions and triggers aren't created

From

Vick Khera

Date:

20 December 2010, 13:32:04

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.