CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.2
Incerteza
Incerteza

Reputation: 34884

Getting the number of the rows affected by update or insert

I need to check how many rows are affected by update or insert (actually, only update at the moment). I created the following function:

CREATE OR REPLACE FUNCTION rows_affected()
  RETURNS integer AS
$BODY$

DECLARE
    x INTEGER := -1;
BEGIN
    GET DIAGNOSTICS x = ROW_COUNT;
    RETURN x;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION rows_affected()
  OWNER TO alex1;

Then I updated a table and called this function, the result was zero. Then I inserted a row and called the function again and again the result was zero. What's up with it? And how do I check the amount of the rows that were affected by update or insert properly?

UPDATE:

This doesn't work as I expect, instead it always returns 0 as the amount of the rows affected:

SELECT rows_affected();

update email set email='fdsfds' where id = 1;

insert into email(email) values('fdsfsdfdsfds');

SELECT rows_affected();

Upvotes: 1

Views: 841

Answers (1)

Stan
Stan

Reputation: 1999

When function/trigger is called, the new internal execution state structure is created (PLpgSQL_execstate) with ROW_COUNT value (eval_processed property) set to 0. So inside function you can get ROW_COUNT only for statements inside the same function. As a workaround you can pass sql statement as the text argument to this procedure and execute it inside.

Upvotes: 1

Related Questions