Thread: How to get response message

How to get response message

From
Rama Krishnan
Date:
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;
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;

End;
$$ language plpgsql;

Re: How to get response message

From
Adrian Klaver
Date:
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



Re: How to get response message

From
Francisco Olarte
Date:
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.



Re: How to get response message

From
"David G. Johnston"
Date:
On Fri, Jun 10, 2022 at 9:38 AM Rama Krishnan <raghuldrag@gmail.com> wrote:
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

If it doesn't error, it was successful.  That is basically the API for a void returning function.

How to get response of the function status

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


Unless you are inspecting test_old outside the function you should just get rid of the table altogether.

Delete has a USING clause, I'd suggest that, with the sales subquery, instead of using IN.

If you want to return a useful count I'd move the delete into a CTE, add RETURNING, count(*) the results, and return that (changing the function output to either integer, text, or json as you desire).

David J.