How to tell if that UPDATE worked?

drevil@sidereal.kz 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.

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

Er - is this another added feature where we're still lacking
documentation?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

Jan Wieck <janwieck@Yahoo.com> writes:

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>;
Er - is this another added feature where we're still lacking
documentation?

After a quick grep, I can state that the absolute sum total of the
documentation on this feature is:

HISTORY: New PL/pgSQL GET DIAGNOSTICS statement for SPI value access (Jan)

Better start writing.

regards, tom lane

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 for the tip. That is exactly what I needed. plpgsql is a
painful language in many ways, but it is also very straightforward, so
I'm writing the bulk of my project in it.

Er - is this another added feature where we're still lacking
documentation?

As pointed out by Tom in a subsequent email, there is one cryptic line
in there, which is probably why I overlooked it...

Thanks for your help

Jan Wieck <janwieck@Yahoo.com> writes:

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>;
Er - is this another added feature where we're still lacking
documentation?

After a quick grep, I can state that the absolute sum total of the
documentation on this feature is:

HISTORY: New PL/pgSQL GET DIAGNOSTICS statement for SPI value access (Jan)

Better start writing.

The good news is that this is a new 7.1 feature, and I haven't checked
all the HISTORY items for documentation updates yet.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026