CREATE OR REPLACE FUNCTION public.merge_all_test2()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
  declare
    agrow RECORD;
    agrid numeric;
    ver numeric;

    missing_agrio CURSOR FOR select * from R_AGRIO_test
      where main_id is null
      order by tagid, blockid, rowdate, device_type, placement;

    missing_brkiofaults CURSOR FOR select * from R_BRKIOFAULTS_test
      where main_brk_id is null;

BEGIN

  with pivot as (
    select
      agr.id as node_id,
      make_r_code(agr.tagid, agr.blockid) as code,
      get_r_lock(make_r_code(agr.tagid, agr.blockid), false) as locked_ver,
      main_agr.id as main_id
    from R_AGRIO_test agr
    left join R_AGRIO main_agr
      -- all of the key columns must be here!
      using ( tagid, blockid, rowdate, device_type, placement )
  )
  update R_AGRIO_test
  set
    locked_version = pivot.locked_ver,
    main_id = pivot.main_id,
    lock_code = pivot.code
  from pivot
  where
    R_AGRIO_test.id = pivot.node_id;

  select count(id) as c from R_AGRIO_test where locked_version <= 0 into agrow;
  if agrow.c > 0 then
    -- $TODO: we used to be able to respond with the code, and ver that
    -- lead to a failure, but now it's more complicated to, but possible,
    -- and should be done if this becomes a problem.
    raise exception USING ERRCODE = 'check_violation',
      MESSAGE = 'Can not obtain lock for ' || agrow.c || ' entries from r_agrio';
  end if;

  -- all entries that have version less than the locked_version can be
  -- removed now (we used to ignore them, but now it's just easier to delete)
  -- note that the expectation that all dependent brk entries will be deleted
  -- by cascade
  delete from R_AGRIO_test where version < locked_version;

  -- now, for all the entries that weren't found in the join with main r_agrio
  -- table, we need to create a new entry there.
  for agrow in missing_agrio loop

    -- this is a bit tricky. We want to just establish the new main R_AGRIO
    -- entries, and then merge into them later, for all of the data set, so
    -- we use proper key values, but 0 data values.
    select merge_agrio(
      null::numeric,
      agrow.tagid,
      agrow.blockid,
      agrow.rowdate,
      0, 0, 0, 0,
      agrow.device_type,
      agrow.placement,
      0, 0) into agrid;

    update R_AGRIO_test set main_id = agrid where current of missing_agrio;

  end loop;

  -- now, let's merge all r_agrio data in bulk!

  with n as (
    select * from R_AGRIO_test
    order by tagid, blockid, rowdate, device_type, placement
  )
  update r_agrio a set
    count = a.count + n.count,
    events = a.events + n.events,
    devents = a.devents + n.devents,
    duration = a.duration + n.duration,
    unserved = a.unserved + n.unserved,
    unconfirmed = a.unconfirmed + n.unconfirmed
  from n
  where a.id = n.main_id;

  -- r_agrio data merged, move to sattelite tables now.
  -- it's the same deal now:
  -- 1. Merge the node table with main table to find which
  --    primary keys to update.
  -- 2. Create missing records, if any
  -- 3. do the actual merge.
  -- Note, that there is no order-by for sattelite tables,
  -- we already would have locked R_AGRIO entry if there is any update.

  -- R_BRKIOFAULTS

  -- main_id : main R_AGRIO.id
  -- sat_id : main R_BRKxxx.id
  -- node_id : node R_BRKxxx.id
  with pivot as (
    select
      brk.agrioid as main_id,
      brk.id as sat_id,
      refed.node_id as node_id
    from (
      select
        b.id as node_id,
        a.main_id as agrioid,
        b.blockid as blockid,
        b.name as name,
        b.type as type,
        coalesce(b.cname,'') as cname,
        b.typeid as typeid
      from R_BRKIOFAULTS_test b
      join R_AGRIO_test a
        on a.id = b.agrioid
    ) refed
    left join R_BRKIOFAULTS brk
      on
        refed.agrioid = brk.agrioid and
        refed.blockid = brk.blockid and
        refed.name = brk.name and
        refed.type = brk.type and
        refed.cname = coalesce(brk.cname, '') and
        refed.typeid = brk.typeid
  )
  update R_BRKIOFAULTS_test
  set
    main_brk_id = pivot.sat_id,
    main_id = pivot.main_id
  from pivot
  where
    R_BRKIOFAULTS_test.id = pivot.node_id;

  for agrow in missing_brkiofaults loop
    select merge_brkiofaults(
        agrow.main_id,
        agrow.blockid,
        agrow.name,
        0,
        agrow.cname,
        agrow.typeid,
        0, 0,
        agrow.auto
      ) into agrid;

    update R_BRKIOFAULTS_test set main_brk_id = agrid
      where current of missing_brkiofaults;

  end loop;

  with n as (
    select * from R_BRKIOFAULTS_test
  )
  update R_BRKIOFAULTS a set
    count = a.count + n.count,
    dcount = a.dcount + n.dcount,
    type = n.type
  from n
  where a.id = n.main_brk_id;

  delete from R_BRKIOFAULTS_test;
  delete from R_AGRIO_test;

END;
$function$