CODEWITHSUNDEEP
sqlpostgresqlsql-update
Casisac
Casisac

Reputation: 85

Return Updated Column into Array Variable

This update query is part of my function 

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 ;

I want to store updated site_id into a integer array variable in my function

Upvotes: 0

Views: 17

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51496

smth like shoud do:

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;

Upvotes: 2

Related Questions