CODEWITHSUNDEEP
sqlpostgresqlplpgsql
tehdima
tehdima

Reputation: 77

insert calculated values into two tables with one function

I have 2 tables and after insert few columns in first table I need calculate other columns and put aggregated values in second table

for example 

a|b|c|       d|e|f|
1|5| |
1|5| |

I need to insert a and b columns of first table into d and e columns in second table, and c column in first table is value of (b-c).

I have a function like

declare
 cursor1 CURSOR FOR
  SELECT SUM(a) as a, SUM(b) as b
begin
 FOR cur_rec in cursor 1
  LOOP
   insert into b(d, e)
   values (cur_rec.a, cur_rec.b)
on conflict etc...

how can I insert in first table c column result of (a-b) calculated in this function? all values ~INT

I want result like 

a|b|c|       d|e|f|
1|5|4|       2|10|8
1|5|4|

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

You seem to want two statements:

update table1
    set c = b - a;

update table2
    set d = t1.a,
        e = t1.b,
        f = t1.c
    from (select sum(a) as a, sum(b) as b, sum(c) as c
          from table1
         ) t1;

Upvotes: 1

Related Questions