On Fri, 10 Jun 2022 at 18:38, Rama Krishnan <raghuldrag@xxxxxxxxx> wrote: > > Hi All, > > I am want to delete old records using function so my senior has function like below but I want to get response of this particular inside query wheter it is successful or failure > How to get response of the function status > Drop table test_old; > Create table test_old as select * from sales where bill_date<now() -interval '1 year'; > Delete table sales where sales_id in (select sales_id from test_old; I do a similar thing routinely and use a "move", ( insert into archive delete from live where yadayada returning whatever ). I suppose you could do a simiar trick. drop table test_old; -- Beware of this, it makes your functiondangerous, if you execute it twice you loose data. create table test_old as delete from sales where bill_date<now() - '1 year'::interval returning *; -- Some tuning may be needed. This approach is normally safer and I've found it faster ( Pg has to locate all the rows and read it for the select, deleting them is normally less work than locating them again, also you only have one condition, which insures you insert exactly what you delete. I would opt for creating the test-old table once, with "like sales", manually and then use a one-line insert-delete-returning, this way your function is much safer. If you execute it twice, second time does nice ( barring some last second sale which might be moved ), if you forget to delete past year from old it is a simple manual delete ( or just ignore the data you already reviewed and delete two years when done ). I think manually creating / truncating test_old is a bit longer but much safer. FOS.
