Re: How to get response message

On 6/10/22 09:37, Rama Krishnan 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
> 
>   create or replace function data_purge() returns void as$$
> Declare
> Begin
> Drop table test_old;

This should probably be:

Drop table IF EXISTS test_old;

Just in case the table has already been dropped.


> 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;

Delete from sales where sales_id in (select sales_id from test_old);

See DELETE for more information:

https://www.postgresql.org/docs/current/sql-delete.html


As to getting execution information see:

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

> 
> End;
> $$ language plpgsql;
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com