On 7/20/22 9:38 AM, Rama Krishnan wrote:
Reply to list also
Ccing list.
> Hi ALL,
>
> I have created the batch wise query but the variable is not working in
> the delete.
>
>
> create or replace function sports_sale() returns void as $$
> declare
> counter integer := 0;
> row_count integer :=0;
> start integer :=1;
>
> begin
>
> SELECT sale_start_count INTO start FROM sale_delete_counter ORDER BY
> ID DESC LIMIT 1;
> SELECT sale_loop_counter INTO counter FROM sale_delete_counter ORDER
> BY ID DESC LIMIT 1;
> SELECT count(*) INTO row_count FROM sports_sale_archive;
> SELECT ceil(row_count/10000) INTO row_count;
> while counter < row_count loop
> raise notice 'Counter %', counter;
> delete from sports_sale where id in (select id from
> sports_sale_archive WHERE id between start and start+9999);
> counter := counter + 1;
> start :=start+10000;
> INSERT INTO
> sale_delete_counter(sale_start_count,sale_loop_counter)
> VALUES(start,counter);
> exit when counter>5;
> end loop;
> end;$$ language plpgsql;
1) I thought this was a date based deletion?
2) How do you know that the ids in "id between start and start+9999"
actually exist?
3) Could this not be simplified to something like?:
create or replace function sports_sale() returns void as $$
declare
counter integer;
begin
select count(*) into counter from sports_sale_archive where
<date/id> between <start> and <end>;
while counter > 0 loop
raise notice 'Counter %', counter;
delete from sports_sale where id in (select id from
sports_sale_archive WHERE id between <start> and <end> order by
<date/id> limit 10000);
counter := counter - 10000;
end loop;
end;$$ language plpgsql;
Not tested and should be taken as starting point as it is not entirely
clear to me what you are trying to achieve.
>
>
> Here I have created the archive table based on created_date with more
> one year data. i want to execute this delete query using batch wise(each
> iteration 10K totally 50K records deletion per execution ). The issue
> was that the variable was not working in deletion subquery.
> Regards
>
> A.Rama Krishnan
>
--
Adrian Klaver
adrian.klaver@aklaver.com
