September 13th, 2003, 02:21 PM
Triggers & Functions, ahhhh....
Here's the dilemma, I'm extremely new to this PostgreSQL and I'm having to implement a lot of triggers which I have never done in any sort of database setting.
I've seen that the popular thing to do with triggers is just to call a separate function to handle the actual work of the trigger. Not a big deal, I've gotten that far. Question is, once inside a function, how do you reference tables outside the one you are trying to update or insert into? I am having 2 separate IDs that both reference different instances of another table. if that was too confusin, which it is to me, I have one table called "Individual" that has a primary key as the "I_ID." I have a table called "Married_Into" that accepts "I_ID_1" and I_ID_2" and a family ID, but that's not a big deal. Problem is, upon insertion or update of "Married Into" table I need to check the genders of the 2 individuals because gay marraige just ain't right ;-) Anyway, I need to be able to reference the Individual table and reference I_ID two separate times by using I_ID_1 and I_ID_2. So, how can I reference Individual table even though the data is actually a tuple of the Married_Into table?
Thanks in advance for any and all help. I am in need of a miracle about now.
September 15th, 2003, 09:33 AM
create a CURSOR that is based on a SELECT from the outside tables that the record is not INSERT/UPDATE-ing to. You can create a REFCURSOR that will be a query def. for the cursor that you define based on the record that is being INSERT/UPDATE-ed
OPEN some_refcursor FOR SELECT gender FROM gender_table WHERE I_ID_1 = NEW.I_ID_1;
FETCH some_refcursor INTO v_I_ID_1; --now you've got the gender of the one;
OPEN some_refcursor FOR SELECT gender FROM gender_table WHERE I_ID_2 = NEW.I_ID_2;
on like that, this is how I would do it in PL/pgSQL
remember to install the language handler function or use createlang plpgsql from your shell.
September 15th, 2003, 05:40 PM
What about this?
SELECT INTO v_I_ID_1 gender FROM gender_table WHERE I_ID_1 = NEW.I_ID_1;
SELECT INTO v_I_ID_2 gender FROM gender_table WHERE I_ID_2 = NEW.I_ID_2;