Re: How to get response message

On Fri, Jun 10, 2022 at 9:38 AM Rama Krishnan <raghuldrag(at)gmail(dot)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.