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;
I have found similar threads on SO but didn't find any relating to both outputting both insert / update and delete in one procedure.
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.