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

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I need to make a after insert trigger that calls a function that takes the last record inserted in a certain table and inserts the ID of that last record into another table according to a select that returns several 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 that does this logic that I showed? Is it possible?
The structures of trigger and function do later.

sql postgresql trigger

asked by anonymous 07.06.2017 / 19:53

2 answers

Receive a single data from a Firebase node event click carouselview xamarin

score 0 · Answer 1

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

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')

score 0 · Answer 2

What you want to do is to insert these ids into a binding table, but as far as I understand this tab_2 returns several ids, then in the end the insert looks 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 tb_2 for same id of db_1 .

What you can do is a FOR that will go through this tab_2 (SELECT ID FROM tb_2 WHERE NOME = 'AAA') , in this example you will have three registers, then just insert each with your new id of tb_2 even 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;