Function with Insert according to a select that returns a list of ID’s - Postgresql

Question

Function with Insert according to a select that returns a list of ID’s - Postgresql

Asked 7 years, 5 months ago

Viewed 1,581 times

0

I need to make a Rigger after insert calling a function that takes the last record inserted into a certain table and inserts the ID of that last record into another table according to a select that returns multiple ID’s. EX:

tb_1  
.-------------.
|  ID  | NOME |  
.-------------.    
|   1  |   A  | 
|   2  |   B  | 
|   3  |   C  | <- Último ID inserido  
'------'------' 

tb_2  
.-------------.
|  ID  | NOME |  
|------|------| 
|  41  |  AAA |  
|  42  |  BBB | 
|  43  |  CCC |  
|  44  |  AAA |  
|  45  |  AAA |  
'------'------'   

INSERT INTO tb_3 (tb_1_id,tb_2_id)  
VALUES (
        (SELECT MAX(ID) FROM tb_1), 
        (SELECT ID FROM tb_2 WHERE NOME = 'AAA')
       )  


tb_3  
.----------.----------.  
|  tb_1_id |  tb_2_id | 
|----------|----------| 
|     3    |    41    |
|     3    |    44    |
|     3    |    45    |
'----------'----------'

How to make a insert what makes this logic that I have shown? It is possible?
The structures of trigger and function I’ll do it later.

I don’t think it’s possible by Trigger because it’s two tables

– Motta

2017/06/07 at 18:33
You basically explained the behavior, it makes sense your logic to me. Try to do, if you have any problem ask a question of the respective problem.

– David

2017/06/08 at 01:27
I want to know if it is possible to make an Insert in this type, because the example I showed was wrong, but the logic is this.

– Spec4422

2017/06/08 at 19:04
Vote today! Vote tomorrow! Vote forever! Vote consciously! Your vote is very important to our community, contribute to us, and help make Stack Overflow in Portuguese (Sopt) bigger and bigger. You can learn more at: Vote early, vote often

– Marcos Henzel

2017/06/14 at 13:42
Have you evaluated the use of the RETURNING clause of the INSERT command? I believe it can facilitate your procedure.

– anonimo

2019/05/03 at 22:10

2 answers

Browser other questions tagged sql database postgresql trigger

You are not signed in. Login or sign up in order to post.

by Athos Bonner • 36 points · Answer 1 · 2017-06-09T03:47:13+00:00

What you want to do is insert these ids in a link table, but from what I understand this tab_2 returns multiple ids, so in the end the insert gets like this:

INSERT INTO tb_3 (tb_1_id,1) 
INSERT INTO tb_3 (tb_1_id,2)
INSERT INTO tb_3 (tb_1_id,3)

Three ids of the tb_2 for the same id of db_1.

What you can do is a FOR who’s gonna walk this tab_2 (SELECT ID FROM tb_2 WHERE NOME = 'AAA'), in this your example will have three records, so just go inserting each one with your new id of tb_2 and the same id of tb_1.

EX:

FOR _table2 in (SELECT ID FROM tb_2 WHERE NOME = 'AAA')

LOOP

   INSERT INTO tb_3 (tb_1_id,tb_2_id) VALUES(SELECT MAX(ID) FROM tb_1,_table2.id)

END LOOP;

by Spec4422 • 11 points · Answer 2 · 2017-06-09T14:21:37+00:00

I found the solution in the simplest way and I have done several times. (so far I have not dropped the plug that I entered this world of loop just for this).

INSERT INTO tb_3 (tb_1_id, tb_2_id) (
     SELECT (SELECT MAX(ID) FROM tb_1) AS tb_1_id, ID AS tb_2_id 
       FROM tb_2 WHERE NOME = 'AAA')