How to return number of rows deleted in postgresql function

Question

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

Gordon Linoff · Accepted Answer · 2017-12-17 16:52:19Z

11

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;

edited Dec 17, 2017 at 16:52

answered Dec 17, 2017 at 16:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Over a year ago

I had to change RETURNS int to RETURNS bigint and then it worked.

Collectives™ on Stack Overflow

How to return number of rows deleted in postgresql function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related