CODEWITHSUNDEEP
postgresql
David Barbata
David Barbata

Reputation: 373

Postgresql function return affected row count

I created a function. I defined returning value void. But I want to show affected row count. How can I do? 

CREATE OR REPLACE FUNCTION update() RETURNS void  AS 
$BODY$
BEGIN
   update test_a set name='cde' where name='abc';
   update test_b set name='mno' where name='klm';
END;
$BODY$   
LANGUAGE plpgsql VOLATILE   
COST 100; 
ALTER FUNCTION update()   
OWNER TO postgres;

Upvotes: 29

Views: 44296

Answers (1)

emacsx
emacsx

Reputation: 546

you should look into GET DIAGNOSTICS, since this is a PLpgSQL function. You might also find the Postgres SQL extension RETURNING * for UPDATE/INSERTS useful.

CREATE OR REPLACE FUNCTION update() RETURNS void  AS 
$BODY$
DECLARE
  a_count integer;
  b_count integer;
BEGIN
   update test_a set name='cde' where name='abc';
   GET DIAGNOSTICS a_count = ROW_COUNT;
   update test_b set name='mno' where name='klm';
   GET DIAGNOSTICS b_count = ROW_COUNT;

   RAISE NOTICE 'The rows affected by A=% and B=%', a_count, b_count ;
END;
$BODY$   
LANGUAGE plpgsql VOLATILE   
COST 100; 
ALTER FUNCTION update()   
OWNER TO postgres;

Depending on what you might want to achieve, the special boolean variable "FOUND" could serve; UPDATE, INSERT, and DELETE statements set FOUND true if at least one row is affected, false if no row is affected.

Upvotes: 53

Related Questions