CODEWITHSUNDEEP
postgresqlhasura
regShank
regShank

Reputation: 235

Postgres function to delete old rows and return deleted rows

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!

Upvotes: 2

Views: 1776

Answers (1)

user330315
user330315

Reputation:

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);

Upvotes: 4

Related Questions