Postgres function to delete old rows and return deleted rows

Question

I want to write a function to delete old rows in table Offers and then return the deleted rows.

CREATE OR REPLACE FUNCTION delOldOffers() RETURNS void AS $$
DELETE FROM "public"."Offers"
 WHERE created_at < now() - interval '7 days'

$$ LANGUAGE sql STABLE;

I wrote the above function but I am not sure how I can return rows that where deleted? If anyone can help that would be really great!

user330315 · Accepted Answer · 2020-07-18 18:10:01Z

4

You need to declare your function to return a table (or setof) and use the returning clause:

CREATE OR REPLACE FUNCTION deloldoffers(p_num_days integer) 
  RETURNS setof offers 
AS $$
  DELETE FROM Offers
  WHERE created_at < current_timestamp - make_interval(days => p_num_days)
  returning *;
$$ LANGUAGE sql;

To use it, you need to select "from" it:

select *
from deloldoffers(10);

answered Jul 18, 2020 at 18:10

user330315

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

1 Comment

regShank Over a year ago

Thanks a lot for the help! Can I do something similar for update also to get the updated rows back - CREATE OR REPLACE FUNCTION updateOldoffers(p_num_days integer) RETURNS setof offers AS $$ UPDATE "public"."Offers" SET active = 'false' WHERE created_at < current_timestamp - make_interval(days => p_num_days) returning *; $$ LANGUAGE sql;

Collectives™ on Stack Overflow

Postgres function to delete old rows and return deleted rows

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