How to convert SQL Server merge with deleted and inserted output

Question

I'm doing an SQL Server to PostgreSQL migration and there are a lot of procedures which are like the below, where we need access to both the inserted / update and deleted data:

CREATE PROCEDURE dbo.CGTACCUMULATED_INSUPD

@AccID int
, @Input tvpCGTACCUMULATED_INSUPD readonly

as

merge CGTAccumulated t
using (
   select  @AccID [AccID]
           , F_YEAR [FinancialYear]
           , F_ACCUMCOST [CarryForwardLoss]
           , F_B1 [OffBook]
           , F_E1 [ExternalISAContribution]
   from    @Input
) s
on t.AccID = @AccID and t.FinancialYear = s.FinancialYear
when matched and isnull(s.CarryForwardLoss, t.CarryForwardLoss) = 0 and isnull(s.OffBook, t.OffBook) = 0
            and isnull(s.ExternalISAContribution, t.ExternalISAContribution) = 0 then
   delete
when matched and (t.CarryForwardLoss <> isnull(s.CarryForwardLoss, t.CarryForwardLoss)
                 or t.OffBook <> isnull(s.OffBook, t.OffBook)
                 or t.ExternalISAContribution <> isnull(s.ExternalISAContribution, t.ExternalISAContribution)) then
   update
   set     CarryForwardLoss = isnull(s.CarryForwardLoss, t.CarryForwardLoss)
           , OffBook = isnull(s.OffBook, t.OffBook)
           , ExternalISAContribution = isnull(s.ExternalISAContribution, t.ExternalISAContribution)
when not matched and (s.CarryForwardLoss <> 0 or s.OffBook <> 0 or s.ExternalISAContribution <> 0) then
   insert  (AccID, FinancialYear, CarryForwardLoss, OffBook, ExternalISAContribution)
   values  (@AccID, s.FinancialYear, isnull(s.CarryForwardLoss, 0), isnull(s.OffBook, 0), isnull(s.ExternalISAContribution, 0))
output
   isnull(deleted.FinancialYear, inserted.FinancialYear) [FinancialYear]
   , deleted.CarryForwardLoss [OldCarryForwardLoss]
   , deleted.OffBook [OldOffBook]
   , deleted.ExternalISAContribution [OldExternalISAContribution]
   , inserted.CarryForwardLoss [NewCarryForwardLoss]
   , inserted.OffBook [NewOffBook]
   , inserted.ExternalISAContribution [NewExternalISAContribution];

What is the best way to convert this into Postgresql? I am thinking along the lines of the below (however this doesn't work because it has two into clauses). Also, creating parameters for every return value seems a bit clunky, is there a better way (perhaps a temporary table or something)?

CREATE PROCEDURE cgtaccumulated_insupd(IN par_accid INTEGER, IN par_input tvpcgtaccumulated_insupd, INOUT p_refcur refcursor)
AS 
$BODY$
DECLARE
    l_old_CarryForwardLoss        double precision;
    l_old_OffBook                 double precision;
    l_old_ExternalISAContribution double precision;
    l_old_FinancialYear           double precision;
    l_new_CarryForwardLoss        double precision;
    l_new_OffBook                 double precision;
    l_new_ExternalISAContribution double precision;
    l_new_FinancialYear           double precision;
BEGIN
    -- This creates a temporary table to hold the composite type data
    PERFORM tvpaccid$aws$f('par_input$aws$tmp');
    INSERT INTO "par_input$aws$tmp"
    SELECT *
      FROM UNNEST(par_input);

    with src as (
        select par_accid    accid
              ,f_year       FinancialYear
              ,f_accumcost  CarryForwardLoss
              ,f_b1         OffBook
              ,f_e1         ExternalISAContribution
          from "par_input$aws$tmp"
    ), deleted as (
    delete from CGTAccumulated tgt
     where tgt.accid in 
        (select accid
           from src
          where tgt.FinancialYear = src.FinancialYear
            and coalesce(src.CarryForwardLoss, tgt.CarryForwardLoss) = 0
            and coalesce(src.OffBook, tgt.OffBook) = 0
            and coalesce(src.ExternalISAContribution, tgt.ExternalISAContribution) = 0)
        returning CarryForwardLoss, OffBook, ExternalISAContribution, FinancialYear
             into l_old_CarryForwardLoss, l_old_OffBook, l_old_ExternalISAContribution, l_old_FinancialYear 
    )
    insert into CGTAccumulated (AccID, FinancialYear, CarryForwardLoss, OffBook, ExternalISAContribution)
    select accid, FinancialYear, CarryForwardLoss, OffBook, ExternalISAContribution
      from src
        on conflict(accid) do update set
            CarryForwardLoss        = coalesce(src.CarryForwardLoss, CarryForwardLoss)
           ,OffBook                 = coalesce(src.OffBook, OffBook)
           ,ExternalISAContribution = coalesce(src.ExternalISAContribution, ExternalISAContribution)
    returning CarryForwardLoss, OffBook, ExternalISAContribution, FinancialYear
         into l_new_CarryForwardLoss, l_new_OffBook, l_new_ExternalISAContribution, l_new_FinancialYear;

    open p_refcur for
        select coalesce(l_old_FinancialYear, l_new_FinancialYear) FinancialYear
              ,l_old_CarryForwardLoss                             OldCarryForwardLoss
              ,l_old_OffBook                                      OldOffBook
              ,l_old_ExternalISAContribution                      OldExternalISAContribution
              ,l_new_CarryForwardLoss                             NewCarryForwardLoss
              ,l_new_OffBook                                      NewOffBook
              ,l_new_ExternalISAContribution                      NewExternalISAContribution;
    
END;
$BODY$
LANGUAGE plpgsql;

Update

I have found similar threads on SO but didn't find any relating to both outputting both insert / update and delete in one procedure.

If this works on SQL Server why migrate? The SQL Server code could be improved imo — SteveC, Aug 02 '21 at 21:23
The full database is being moved over from SQL Server to Postgres. I didn't write the SQL Server code, it is legacy code from many years ago which I'm helping to convert. — RLOG, Aug 03 '21 at 06:44
Is the reason the migration is happening because the MERGE statement above is really slow? — SteveC, Aug 04 '21 at 12:00
No it's nothing to do with this particular statement, there are thousands of procedures that are being migrated, the above is just meant to be an example where they are using the SQL Server insert and delete tables that I need to translate to Postgres. — RLOG, Aug 04 '21 at 12:12

score 1 · Accepted Answer · answered Aug 05 '21 at 21:16

Here is an example. I used a simplified table structure. Tested on postgresql 13.

create table test (
  id int primary key
 ,name varchar(100)
 ,amount int    
)

-- initial data   
with src as (
select * 
  from (values 
         (1, 'a', 100) 
        ,(2, 'b', 200)
        ,(3, 'c', 300)
        ,(4, 'd', 400)
       ) as t(id, name, amount)
)   
insert into test
select *
  from src

-- simulate merge and return deleted and inserted  
with src as (
select * 
  from (values 
         (1, 'a', 101) 
        ,(4, 'd', 0)
       ) as t(id, name, amount)
)   
,deleted as (
delete 
  from test 
 where id in (select id from src where amount = 0)
returning * 
)
,inserted as (
insert into test
select *
  from src 
 where src.amount != 0  
 on conflict(id) do update set 
    amount = excluded.amount
 returning *
)
select 'deleted' rec_type, d.* from deleted d
union all
select 'inserted / updated', i.* from inserted i

I put the records inserted and updated in one bucket. You can easily separate them as needed.

Thanks. I ended up using a full join for this specific use case as there is only one row being processed and there were multiple possible dml operations so it fit better with the return value, however union all makes sense when there are multiple rows. — RLOG, Aug 08 '21 at 21:19

How to convert SQL Server merge with deleted and inserted output

Update

1 Answers1