Getting number of affected records

From: Basil Bourque <basil(dot)list(at)me(dot)com>
To: seapug(at)postgresql(dot)org
Subject: Getting number of affected records
Date: 2010-10-31 19:30:45
Message-ID: 5553E7CB-5FD5-4583-A586-D4E54B3F271F@me.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: seapug

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

Browse seapug by date

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