On Fri, Jun 10, 2022 at 9:38 AM Rama Krishnan <raghuld...@gmail.com> wrote:
> 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). David J.
