How to combine two rows in a table postgresql?

Question

Normaly I wouldn't ask this question however I have a bit of unique situation where I'm having troudle in How to do it.

I have a table A as follows:

uniqueid , itemid, quantity, selected
1            2455     10        TRUE
2            7458     50        FALSE
3            58494    20.6      FALSE
4            2455     20        TRUE

I need to write a combine function that takes rows which are TRUE of the same part and combine them to one row (with the updated quantity).

meaning I want to get:

uniqueid , itemid, quantity, selected
1            2455     30        FASLE
2            7458     50        FALSE
3            58494    20.6      FALSE/TRUE (doesn't matter)

OR:

uniqueid , itemid, quantity, selected
2            7458     50        FALSE
3            58494    20.6      FALSE
4            2455     30        FALSE/TRUE (doesn't matter)

My function doesn't get any paramenters... I need some method which I can identfy and work with the rows which are selected. First I thought of doing:

CREATE OR REPLACE FUNCTION func1()
  RETURNS void AS 
$BODY$
declare 
    ROW RECORD
begin
for ROW in select * from A where selected
    LOOP
        do combine code     
    end loop;
end;                
$BODY$
  LANGUAGE plpgsql VOLATILE

However this code will not work as after each combine two rows become one. In the above example the loop will generate two iterations, but I need only 1 iteration.. only 1 combine action neeeded.

I only need assistance in how to get the structure of the function - loop? if? how to save the rows?. no need to write the combine code.

To make it simpler assume there can be only two rows which selected is TRUE.

Note: func1 is supposed to save the new state into A.

Answer 1

A simple solution would be to do this in a union - in that case it also doesn't matter how many rows there are with selected = false:

select min(uniqueid) as uniqueid,
       itemid, 
       sum(quantity) as quantity, 
       false as selected
from a 
where selected
group by itemid
union all
select uniqueid,
       itemid,
       quantity,
       selected
from a
where not selected
order by 1;

---

Edit after it has been clarified that the table should be modified.

You can do this with a data modifying CTE. In the first step the sum of the quantity is updated and in the second step the no longer needed rows are deleted:

with updated as (
  -- this updates the lowest uniqueid with the total sum
  -- of all rows. If you want to keep/update the highest
  -- uniqueid change the min() to max()
  update a 
    set quantity = t.total_sum,
        selected = false
  from (
    select min(uniqueid) as uniqueid,
           itemid, 
           sum(quantity) as total_sum
    from a 
    where selected
    group by itemid
  ) t
  where t.uniqueid = a.uniqueid 
  returning a.uniqueid
)
-- this now deletes the rows that are still marked
-- as "selected" and where not updated
delete from a
where selected
  and uniqueid not in (select uniqueid from updated);

This assumes that the uniqueid column is indeed unique (e.g. a primary key or has a unique index/constraint defined). The value for selected column has to be changed in order for this to work. So it does matter if selected is set to false during this process.

Answer 2

You did not tell anything about uniqueid transmutation and selected column as well, but I assume you want smth like this:

select distinct uniqueid,itemid,quantity,selected
from (
select 
 case when selected then max(uniqueid) over (partition by selected) else uniqueid end uniqueid 
 , itemid
 , case when selected then sum(quantity) over (partition by selected) else quantity end quantity 
 , false  selected
from t113
) init
order by 1
;

Answer 3

You might want to give a try with a UNION maybe

SELECT u.* 
FROM (
  SELECT MIN(t.uniqueid) AS uniqueid,
    t.itemid,
    SUM(t.quantity) AS quantity,
    FALSE AS selected
  FROM table_name t
    WHERE t.selected = TRUE
    GROUP BY t.itemid
  UNION
  SELECT t.uniqueid,
    t.itemid,
    t.quantity,
    t.selected
  FROM table_name t
    WHERE t.selected = FALSE) u
  ORDER BY u.uniqueid ASC