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(dot)klaver(at)aklaver(dot)com