DELETE query returning count of records deleted

user_4406670 0
Viewed 1417

Using postgres 9.6.

I am attempting to write a function that will delete records and return to the application developer the number of rows that were deleted. I have some the following demo but I keep running into a syntax issue. 

BEGIN;
DELETE FROM it.animals WHERE id <5
RETURNING COUNT(id);
ROLLBACK;

[2020-03-12 09:14:11] [42803] ERROR: aggregate functions are not allowed in RETURNING

postgresql
1 Answers

You need to use a common table expression:

with deleted as (
   DELETE FROM it.animals WHERE id <5
   returning id
)
select count(*)
from deleted;

However if that is part of a PL/pgSQL function, you can use GET DIAGNOSTICS:

$$
declare 
  l_num_rows integer;
begin
  DELETE FROM it.animals WHERE id <5
  GET DIAGNOSTICS l_num_rows = ROW_COUNT;  
  return l_num_rows;
end;
$$
user_3303150
Related