sql - 将更新的列返回到数组变量

此更新查询是我的功能的一部分

update  tax_table it
    set updated_by   =  1,
        updated_on   =  now(),
        customer_id  =  gs.colum,
        site_id      =  t.colum
    from    table1 ri
        inner join table2 t on t.colum = ri.colum
        inner join table3 gs on ri.colum = gs.colum
        inner join table4 vg on vg.colum = ri.colum
    where   ri.table1 = _id ;

我想将更新的site\u id存储到函数中的整数数组变量中

最佳答案

像应该做的那样：

with u as (
update  tax_table it
    set updated_by   =  1,
        updated_on   =  now(),
        customer_id  =  gs.colum,
        site_id      =  t.colum
    from    table1 ri
        inner join table2 t on t.colum = ri.colum
        inner join table3 gs on ri.colum = gs.colum
        inner join table4 vg on vg.colum = ri.colum
    where   ri.table1 = _id
    returning site_id
)
select array_agg(site_id) from u into YOUR_VAR;