Function inside query status

On 6/29/22 23:57, Rama Krishnan wrote:

Hi All,

Is it possible to check the query execution status inside the function.

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

1) You are not actually declaring anything so the Declare is not needed.

2) You have three queries in the function do you want to check them all
or some subset?

3) What exactly are checking for?

4) Have you looked at?:

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

and

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-EXCEPTION-DIAGNOSTICS

End;
$$ language plpgsql;

--
Adrian Klaver
adrian.klaver@aklaver.com