CODEWITHSUNDEEP
postgresql
Michael
Michael

Reputation: 1009

How to return number of rows deleted in postgresql function

Here is a very simplified example of what I am trying to do. Consider this table of people:

CREATE TABLE people (pid SERIAL PRIMARY KEY, firstname TEXT, isalive BOOLEAN);

INSERT INTO people (firstname, isalive) VALUES
    ('Sam', TRUE),
    ('Leslie', FALSE),
    ('Parker', FALSE);

Now I want to delete dead people from the table. When I do this in psql, I see a message telling me how many rows were deleted.

postgres=> DELETE FROM people WHERE isalive = FALSE;
DELETE 2
postgres=>

However, if I put this delete statement into a function and call it, I don't see the same message.

CREATE OR REPLACE FUNCTION deletedead() RETURNS void AS $$
  DELETE FROM people WHERE isalive = FALSE;
$$ LANGUAGE SQL;

SELECT deletedead();

This deletes the rows as expected, but it doesn't give any message saying how many rows were deleted. How do I get a message like "DELETE 2" from this function?

I tried modifying my function to return the number of rows deleted:

-- This doesn't work
CREATE OR REPLACE FUNCTION deletedead() RETURNS int AS $$
  DELETE FROM people WHERE isalive = FALSE RETURNING COUNT(*);
$$ LANGUAGE SQL;

... but I get an error saying ERROR: aggregate functions are not allowed in RETURNING

Upvotes: 8

Views: 6584

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

The error message is correct. You should be able to do:

CREATE OR REPLACE FUNCTION deletedead() RETURNS bigint AS $$
  WITH d as (
        DELETE FROM people WHERE isalive = FALSE RETURNING *
       )
  SELECT COUNT(*)
  FROM d;
$$ LANGUAGE SQL;

Upvotes: 11

Related Questions