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

Question

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

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_id 存储到一个整型数组变量中

Answer 1

应该做的事:

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;