CODEWITHSUNDEEP
postgresql
vdegenne
vdegenne

Reputation: 13308

How can I apply a procedure on each row of a table in PostgreSQL (not a trigger)

let's say I have

t1

 a | b  | t2_a
---+----+------
 1 | 10 |
 2 | 11 |
 3 | 12 |
 4 | 14 |
 5 | 16 |

(all values in t2_a are null, I just add the column)

and t2

 a | b
---+----
 1 | 11
 2 | 12
 3 | 13
 4 | 16

I need to add a new line in t2 when t1.b is not in t2.b, so after procedure, t2 becomes :

 a | b  
---+----
 1 | 11
 2 | 12
 3 | 13
 4 | 16
 5 | 10
 6 | 14

and now I need to update t1 to satisfy this condition (t1.t2_a=t2.a AND t1.b=t2.b), so after the procedure t1 becomes :

 a | b  | t2_a
---+----+------
 1 | 10 |   5
 2 | 11 |   1
 3 | 12 |   2
 4 | 14 |   6
 5 | 16 |   4

I don't need the algorithm, I just need to know what are the available PostgreSQL mechanisms to perform such an altering on every row of a table.

I thought I would make a trigger and a PostgreSQL procedure before or after an update on t1 but I am not updating anything and I guess I can't just write UPDATE t1 to trigger.. my trigger.

Upvotes: 0

Views: 223

Answers (1)

klin
klin

Reputation: 121889

I assume that the column a is serial in both tables.

The first operation is insert ... select ...:

insert into t2(b)
select b
from t1
where not exists (
    select 1
    from t2
    where t2.b = t1.b)
returning *;

 a | b  
---+----
 5 | 10
 6 | 14
(2 rows)

For the other use update ... from ...:

update t1
set t2_a = t2.a
from t2
where t1.b = t2.b
returning t1.*;

 a | b  | t2_a 
---+----+------
 1 | 10 |    5
 2 | 11 |    1
 3 | 12 |    2
 4 | 14 |    6
 5 | 16 |    4
(5 rows)

Upvotes: 2

Related Questions