Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

seapug by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group