Finding the number of rows affected by UPDATE or INSERT?

I am writing a bunch of stuff in pl/pgsql, and some of these functions
do UPDATEs or INSERTs on various tables. How could I find out, from
within the pl/pgsql function, how many rows were affected? This way,
if the update or insert fails for some reason, I can return a "failed"
message from the function, instead of pretending that everything is
ok.

I think you want GET DIAGNOSTICS. See
http://www.postgresql.org/idocs/index.php?plpgsql-description.html

Here's the relevant section:
24.2.5.4. Obtaining other results status

GET DIAGNOSTICS variable = item [ , ... ]
This command allows retrieval of system status indicators. Each item is a
keyword identifying a state value to be assigned to the specified variable
(which should be of the right datatype to receive it). The currently
available status items are ROW_COUNT, the number of rows processed by the
last SQL query sent down to the SQL engine; and RESULT_OID, the Oid of the
last row inserted by the most recent SQL query. Note that RESULT_OID is only
useful after an INSERT query.

HTH,

-- Joe

I found out how to do it:

In pl/pgsql:

INT nr;

UPDATE ....
GET DIAGNOSTICS SELECT PROCESSED INTO nr;

will put the number of rows into nr. It's a very useful feature to
have this in PG7.1.

Note that a zero rowcount does not necessarily mean an error; it may just
mean that the WHERE clause in your UPDATE was not satisfied. Which may be
equivalent to an error in your particular application: just not as a general
case.

----- Original Message -----
From: "Dr. Evil" <drevil@sidereal.kz>
To: <pgsql-general@postgresql.org>
Sent: Thursday, August 30, 2001 3:05 AM
Subject: [GENERAL] Solution found! was Re: Finding the number of rows
affected by UPDATE or INSERT?