Thread: 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

On Fri, 10 Jun 2022 at 18:38, Rama Krishnan <raghuldrag@gmail.com> 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
thisparticular inside query wheter it is successful or failure
 

> How to get response of the function status

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

I do a similar thing routinely and use a "move", ( insert into archive
delete from live where yadayada returning whatever ). I suppose you
could do a simiar trick.

drop table test_old; -- Beware of this, it makes your
functiondangerous, if you execute it twice you loose data.
create table test_old as delete from sales where bill_date<now() - '1
year'::interval returning *; -- Some tuning may be needed.

This approach is normally safer and I've found it faster ( Pg has to
locate all the rows and read it for the select, deleting them is
normally less work than locating them again, also you only have one
condition, which insures you insert exactly what you delete.

I would opt for creating the test-old table once, with "like sales",
manually and then use a one-line insert-delete-returning, this way
your function is much safer. If you execute it twice, second time does
nice ( barring some last second sale which might be moved ), if you
forget to delete past year from old it is a simple manual delete ( or
just ignore the data you already reviewed and delete two years when
done ). I think manually creating / truncating test_old is a bit
longer but much safer.

FOS.