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
If it doesn't error, it was successful. That is basically the API for a void returning function.
How to get response of the function status
create or replace function data_purge() returns void as$$
Declare
Begin
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;
Unless you are inspecting test_old outside the function you should just get rid of the table altogether.
Delete has a USING clause, I'd suggest that, with the sales subquery, instead of using IN.
If you want to return a useful count I'd move the delete into a CTE, add RETURNING, count(*) the results, and return that (changing the function output to either integer, text, or json as you desire).
