Re: getting number of rows updated within a procedure

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(at)Yahoo(dot)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/