My database driver does not return the number of rows affected by an execution of INSERT or UPDATE. I put in a feature request. As a workaround, I thought I'd make use of Postgres' RETURNING syntax. 

Some googling led me to believe that the following PL/pgSQL function would return the number of affected records. 

----
CREATE OR REPLACE FUNCTION countAffected() RETURNS integer AS $$

DECLARE
    x integer := -1;
BEGIN
    GET DIAGNOSTICS x = ROW_COUNT;
    RETURN x;
END;
$$ LANGUAGE plpgsql;
----

I successfully created that function. The problem is that I always get zero.  I called it from the RETURNING clause of an INSERT statement, but got zero. I called it after calling COMMIT, but still got zero. I verified the record was successfully inserted.

My SQL to INSERT:
----
INSERT INTO login_attempt_ VALUES ( 
  '3A0E42DA-82DD-46E0-9B13-F1DE9EA307DB', 
  '2010-10-31 12:17:54', 
  'Basil')
RETURNING
 countAffected()
;
----

I believe my function is working. I created a similar bogus function to always return '77'. That bogus function works, as I wrote in my blog, "My First Postgres Function".
http://crafted-software.blogspot.com/2010/10/my-first-postgres-function-plpgsql.html

Any suggestions as to (a) what I'm doing wrong, or (b) another way to retrieve number of records affected?

--Basil Bourque

seapug by date

Next:	From: ndx	Date: 2010-11-01 08:02:38
	Subject: International brand wholesale
Previous:	From: Richard Broersma	Date: 2010-10-29 23:03:59
	Subject: Call For Talks: PGDay LA @ SCALE 9X