Re: How to get response message

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Fri, Jun 10, 2022 at 9:38 AM Rama Krishnan <raghuldrag@xxxxxxxxx> 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.