我正在做一个SQL Server到PostgreSQL的迁移，有很多像下面这样的过程，我们需要访问插入/更新和删除的数据:
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];
把这个转换成Postgresql最好的方法是什么?我正在思考下面的路线(然而这不起作用，因为它有两个into子句)。此外，为每个返回值创建参数似乎有点笨拙，是否有更好的方法(可能是临时表或其他东西)?
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;
更新我在SO上发现了类似的线程，但没有发现任何与在一个过程中输出插入/更新和删除相关的线程。
下面是一个例子。我使用了一个简化的表格结构。在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
我将插入和更新的记录放在一个桶中。您可以根据需要轻松地将它们分开。