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

Thanks

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


>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>