Count rows affected by DELETE

Question

I use this code to verify the DELETE sentence, but I am sure you know a better way:

CREATE OR REPLACE FUNCTION my_schema.sp_delete_row_table(table_name character varying
                                                       , id_column character varying
                                                       , id_value integer)
  RETURNS integer AS
$BODY$
  DECLARE
    BEFORE_ROWS integer;
    AFTER_ROWS integer;
  BEGIN
    EXECUTE 'SELECT count(*) FROM ' || TABLE_NAME INTO BEFORE_ROWS;
    EXECUTE 'DELETE FROM ' || TABLE_NAME || ' WHERE ' || ID_COLUMN || ' = ' || (ID_VALUE)::varchar;
    EXECUTE 'SELECT count(*) FROM ' || TABLE_NAME INTO AFTER_ROWS;

    IF BEFORE_ROWS - AFTER_ROWS = 1 THEN
      RETURN 1;
    ELSE
      RETURN 2;
    END IF;
  EXCEPTION WHEN OTHERS THEN
    RETURN 0;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

How to improve this code? I need it to work in Postgres 8.4, 9.1 and 9.2.

The currently accepted answer is incorrect. Consider the second answer. — Erwin Brandstetter, Jun 01 '15 at 01:09

score 1 · Answer 1 · edited May 23 '17 at 12:24

Actually, you cannot use FOUND with EXECUTE. The manual:

Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND.

There are a couple of other things that might be improved. First of all, your original is open to SQL injection. I suggest:

CREATE OR REPLACE FUNCTION my_schema.sp_delete_row_table(table_name regclass
                                                       , id_column  text
                                                       , id_value   int
                                                       , OUT del_ct int) AS
$func$
BEGIN
   EXECUTE format ('DELETE FROM %s WHERE %I = $1', table_name, id_column);
   USING id_value;                     -- assuming integer columns

   GET DIAGNOSTICS del_ct = ROW_COUNT; -- directly assign OUT parameter

EXCEPTION WHEN OTHERS THEN
   del_ct := 0;
END
$func$  LANGUAGE plpgsql;

format() requires Postgres 9.1 or later. You can replace it with string concatenation, but be sure to use escape the column name properly with quote_ident()!
The rest works for 8.4 as well.

Closely related answers:

Yes Erwin Brandstetter, I use EXECUTE and after GET DIAGNOSTICS. I use only FOUN for INSERT, UPDATE, DELETE sentences. — Aron, Dec 28 '13 at 04:28

score 0 · Accepted Answer · answered Dec 27 '13 at 11:04

0

Look into the variables called found and row_count:

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

found is true if any rows were affected. row_count gives you the number of affected rows.

IF FOUND THEN
  GET DIAGNOSTICS integer_var = ROW_COUNT;
END IF;

answered Dec 27 '13 at 11:04

Denis de Bernardy

75,850
13
131
154

EXECUTE 'DELETE FROM ' || TABLE_NAME || ' WHERE ' || ID_COLUMN || ' = ' || (ID_VALUE)::varchar; – Aron Dec 27 '13 at 12:36
GET DIAGNOSTICS AFFECTED_ROWS = ROW_COUNT; – Aron Dec 27 '13 at 12:37
IF AFFECTED_ROWS = 1 THEN RETURN 1; END IF; – Aron Dec 27 '13 at 12:37
1

Those statements could probably all be changed: `RETURN FOUND::int`; – Denis de Bernardy Dec 27 '13 at 12:40
Denis, I use FOUND after INSERT, UPDATE, DELETE sentences, but I can not use FOUND after EXECUTE. Because, EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND. – Aron Dec 27 '13 at 13:02
1

Fair point. Btw, you should really escape the variables in those statements to avoid sql injections. Use `quote_ident()` and `quote_literal()`, per the docs on the same page I pointed you to. – Denis de Bernardy Dec 27 '13 at 13:03
Yes Denis, You are right: Now, I use: EXECUTE 'DELETE FROM ' || quote_ident(TABLE_NAME) || ' WHERE ' || quote_ident(ID_COLUMN) || ' = ' || quote_literal(ID_VALUE); – Aron Dec 27 '13 at 13:31
And, I use: IF FOUND THEN GET DIAGNOSTICS AFFECTED_ROWS = ROW_COUNT; IF AFFECTED_ROWS = 1 THEN RETURN 1; END IF; END IF; RETURN 0; – Aron Dec 27 '13 at 13:32
You are mistaken concerning `FOUND`, which is not set by `EXECUTE`. – Erwin Brandstetter Dec 27 '13 at 21:28

Count rows affected by DELETE

2 Answers2

Linked