create table purge_data_queue (
data_id bigint not null primary key
);
/* Identify all records to be purged */
insert into purge_data_queue (data_id)
select data_id
from data
where <records to delete>
;
create or replace function purge_data (_limit int default 1000)
returns int
as
$$
declare
_rowcnt int;
begin
create temp table if not exists purge_set (
data_id bigint not null
, primary key (data_id)
) on commit drop
;
/* Identify batch to be purged */
insert into purge_set (
data_id
)
select data_id
from purge_data_queue
limit _limit
for update skip locked
;
/* Delete batch from base table */
delete from data using purge_set
where data.data_id = purge_set.data_id
;
get diagnostics _rowcnt = ROW_COUNT;
/* Delete batch from queue table */
delete from purge_data_queue using purge_set
where purge_data_queue.data_id = purge_set.data_id
;
return _rowcnt;
end;
$$ language plpgsql
set search_path = schema_name
;
