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
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 |