Count Rows Affected By Delete

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

Solution 1:

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:

CREATEOR REPLACE FUNCTION my_schema.sp_delete_row_table(table_name regclass
                                                       , id_column  text
                                                       , id_value   int
                                                       , OUT del_ct int) AS
$func$
BEGINEXECUTE format ('DELETE FROM %s WHERE %I = $1', table_name, id_column);
   USING id_value;                     -- assuming integer columnsGET 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:

• Dynamic SQL (EXECUTE) as condition for IF statement
• Table name as a PostgreSQL function parameter

Solution 2:

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 THENGET DIAGNOSTICS integer_var = ROW_COUNT;
END IF;