Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to tell if that UPDATE worked?

How to tell if that UPDATE worked?

From
Date:
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.

Thanks for any tips.

Re: How to tell if that UPDATE worked?

From
Jan Wieck
Date:
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 #

Re: How to tell if that UPDATE worked?

From
Tom Lane
Date:
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

Re: How to tell if that UPDATE worked?

From
Date:
>     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

Re: How to tell if that UPDATE worked?

From
Bruce Momjian
Date:
> 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