Re: pg_affected Change Request

Michael,

I've never seen that syntax. Is that ANSI standard?

The last SQL database I used did not require that syntax to return the
"affected" count I needed.

Is there any industry standard concerning the implementation of "affected"?

Thanks

----- Original Message -----
From: "Michael Fuhr" <mike(at)fuhr(dot)org>
To: "Jan" <jan(at)fastpitchcentral(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, February 10, 2005 12:31 PM
Subject: Re: [GENERAL] pg_affected Change Request

> On Thu, Feb 10, 2005 at 05:56:33AM -0500, Jan wrote:
> >
> > I write a program that mines data from a small few websites. I revisit
> > those websites on a daily basis. I find a matching key (actually two
fields
> > comprise my unique key) and with the data collected on this visit I
attempt
> > to UPDATE an existing record. I want to know whether I just changed the
> > data or that the data collected is the same as on my last visit.
> >
> > I use PHP. If I check pg_affected_rows($result) I find one record is
always
> > "affected" even when no data has actually changed. Nothing has changed
so
> > the rows affected should be zero. The "affected" is actually
"attempted".
>
> PostgreSQL stores a new version of each row regardless of whether
> the update changed any columns or not, so in that sense all of the
> rows were "affected." Presumably there's a reason for doing this,
> although at the moment I'm not remembering why.
>
> The following is a bit ugly, but if you want to update only those
> rows where a value has changed, then you could do something like
> this:
>
> UPDATE tablename SET col1 = <col1value>, col2 = <col2value>, ...
> WHERE keycol = <keyvalue>
> AND (col1 IS DISTINCT FROM <col1value> OR
> col2 IS DISTINCT FROM <col2value> ...)
>
> This statement uses IS DISTINCT FROM instead of <> so the comparisons
> will handle NULLs properly. If the columns are all NOT NULL then
> you could use <>.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>