deleting and returning data in PostgreSQL

Question

Hello,

I have the following problem: my table is big enought (millions of data rows), this is temporary data which I process. I need to select max and min of one column accrding to some criteria, process this information and remove data according to the same criteria. Actually, the simplest implementation looks like:

select max(col), min(col) from _TABLE_ where _CONDITION_;
...
delete from _TABLE_ where _CONDITION_;

table is big, and when I process it, quering that way in cycle, it takes some time. I thought I can optimize it using "returning" in delete, like

delete from _TABLE_ where _CONDITION_ returning max(col), min(col);

it would be absolutely what I need, but... it does not work at all :) talking that I can't use aggregate functions in returning clause...

is there any good way to improve two queries (selecting max/min of data and deleting the same data) making one query instead? Any trick?

thank you in advance for any information, Maxym

Answer 1

Use a function like this:

create temporary table test (value int);
insert into test select generate_series(1,100);

create or replace function delete_even_from_test_and_return_min_deleted()
  returns int as
$$
declare
  _value record;
  min int;
begin
  min=X'7FFFFFFF'; -- INT_MAX
  for _value in
    delete from test where value%2=0 returning value
  loop
    if min>_value.value then
      min=_value.value;
    end if;
  end loop;
  return min;
end;
$$ language plpgsql;

select count(*) from test;
100

select delete_even_from_test_and_return_min_deleted();
2

select count(*) from test;
50