Thread: getting number of rows updated within a procedure

I'm writing a procedure and I want to check the number of rows updated.
  But I can't figure out how.  I know UPDATE returns "UPDATE #" but I
don't know how to use that to get what I need.

Here's an example:

CREATE FUNCTION "test" () RETURNS int AS ' DECLARE
v_number_of_rows int;

BEGIN           update bigtable           set email_confirmed = 1
where user_group  = 1234;

-- and here, I want to know the number of rows updated
v_number_of_rows := *what?*;

END; ' LANGUAGE 'plpgsql';


Thanks.

--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/
       FreshPorts - http://freshports.org/
     NZ Broadband - http://unixathome.org/broadband/

On Sun, Jan 14, 2001 at 23:27:06 +1300, Dan Langille wrote:
> I'm writing some stuff in PL/pgsql (actually, a lot of stuff).  I have a
> question: At various times, it does UPDATEs.  Is there a way to tell if
> the UPDATE actually affected any rows or not?  I couldn't see how to get
> UPDATE to return anything.

Quoting a recent message by Jan Wieck <janwieck@Yahoo.com>:
:Do a
:
:    GET DIAGNOSTICS SELECT PROCESSED INTO <int4_variable>;
:
:directly  after  an  INSERT,  UPDATE  or DELETE statement and you'll know
:how many rows have been hit.
:
:Also you can get the OID of an inserted row with
:
:    GET DIAGNOSTICS SELECT RESULT INTO <int4_variable>;

HTH,
Ray
--
"The software `wizard' is the single greatest obstacle to computer literacy
since the Mac."
    http://www.osopinion.com/Opinions/MichaelKellen/MichaelKellen1.html

On 15 Jan 2001, at 8:44, J.H.M. Dassen (Ray) wrote:

> On Sun, Jan 14, 2001 at 23:27:06 +1300, Dan Langille wrote:
> > I'm writing some stuff in PL/pgsql (actually, a lot of stuff).  I have a
> > question: At various times, it does UPDATEs.  Is there a way to tell if
> > the UPDATE actually affected any rows or not?  I couldn't see how to get
> > UPDATE to return anything.
>
> Quoting a recent message by Jan Wieck <janwieck@Yahoo.com>:
> :Do a
> :
> :    GET DIAGNOSTICS SELECT PROCESSED INTO <int4_variable>;
> :
> :directly  after  an  INSERT,  UPDATE  or DELETE statement and you'll know
> :how many rows have been hit.
> :
> :Also you can get the OID of an inserted row with
> :
> :    GET DIAGNOSTICS SELECT RESULT INTO <int4_variable>;

Thanks Ray!  That sounds just like what I want.  Unfortunately, it
appears I need a patch.  See:

<http://postgresql.readysetnet.com/mhonarc/pgsql-sql/1999-
07/msg00231.html>

I'm tracking that down now.  FWIW, here's what I tried:

CREATE FUNCTION test2(int4) RETURNS int4 AS '
   DECLARE
      ElementID     ALIAS for $1;
      numrows       int4;
   BEGIN
      update test
         set col1 = ElementID;

      GET DIAGNOSTICS SELECT PROCESSED INTO numrows;
      return numrows;
   END;
' LANGUAGE 'plpgsql';


FreshPorts2=# select test2(12);
ERROR:  parser: parse error at or near "get"

I'll report back when I learn more.  Cheers.

FreshPorts2=# select version();
                             version
-----------------------------------------------------------------
 PostgreSQL 7.0.3 on i386-unknown-freebsdelf4.2, compiled by cc
(1 row)

--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/
       FreshPorts - http://freshports.org/
     NZ Broadband - http://unixathome.org/broadband/