Re: pg_affected Change Request

On Fri, Feb 11, 2005 at 09:26:22AM -0500, Jan wrote:
>
> I've never seen that syntax. Is that ANSI standard?

If you're referring to IS DISTINCT FROM, it's defined in SQL:1999
and SQL:2003.

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

That database probably had a different interpretation of "affected".
MySQL 4.1.9, for example, reports the following:

UPDATE foo ...
Query OK, 3 rows affected (0.02 sec)
Rows matched: 5 Changed: 3 Warnings: 0

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

I'm not sure if this is the correct reference, but I'm looking at
a draft[1] of SQL:2003, Part 2: Foundation, 22.1 <get diagnostics
management>, p. 1064, where it describes ROW_COUNT. On that page
are the following paragraphs (line breaks changed):

1) If S does not contain a <search condition>, then the value of
ROW_COUNT is the cardinality of T before the execution of S.

2) Otherwise, let SC be the <search condition> directly contained
in S. The value of ROW_COUNT is effectively derived by executing
the statement:

SELECT COUNT(*)
FROM T MCN
WHERE SC

before the execution of S.

If that's an appropriate reference and if I'm reading it correctly,
then PostgreSQL's behavior appears to comply with the standard.
Maybe one of the developers can confirm or deny.

[1] http://www.wiscorp.com/sql/sql_2003_standard.zip

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/