Re: Finding the number of rows affected by UPDATE or INSERT?

From: "Joe Conway" <joseph(dot)conway(at)home(dot)com>
To: "Dr(dot) Evil" <drevil(at)sidereal(dot)kz>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Finding the number of rows affected by UPDATE or INSERT?
Date: 2001-08-30 06:36:29
Message-ID: 00d001c1311e$1a5fa420$0705a8c0@jecw2k1
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

> 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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Alexey Borzov 2001-08-30 07:55:30 Re[2]: RFC: PostgreSQL and MySQL comparison.
Previous Message Dr. Evil 2001-08-30 05:57:22 Finding the number of rows affected by UPDATE or INSERT?