I have an INSERT trigger in PostgreSQL that I'm trying to have join the inserted row on another table, and then insert the result in a third table. Let's call the original table, that the INSERT trigger fires on, "A"; the table the cursor joins A on "B"; and the table the trigger function then inserts to "C".
My thinking was that an AFTER INSERT function should allow me to pass a value from the "NEW" row as a parameter in order to reference its corresponding row in Table A, like this:
myCursor CURSOR (insertedKey A.key%TYPE) FOR
SELECT *
FROM A
INNER JOIN B
ON A.key=B.key
WHERE A.key=insertedKey;
...
OPEN myCursor (NEW.key);
FETCH NEXT FROM myCursor INTO row_C;
INSERT INTO C VALUES (row_C.*);
This gives me an empty cursor. If I trigger the trigger on AFTER UPDATE, it works, but with the old row from A. This leads me to think that PostgreSQL doesn't think AFTER INSERT/UPDATE means what I think it means.
Or maybe I'm just doing something wrong? Is there any way of doing what I'm trying to do?
Not sure why it happens but you could do something along the line of
INSERT INTO C
SELECT NEW.*, B.*
FROM B
WHERE B.key = NEW.key