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