I am writing a trigger/function to make certain a default item, and its key exist when an insert is called. EG
The trigger gets called on insert to T1 If column c1 is NULL in the NEW structure, I need to check table t2 to get the key associated with the default for this column. However, if the default is not yet inserted into t2, I an to go ahead and insert it. I found this page: https://stackoverflow.com/questions/6560447/can-i-use-return-value-of-insert-returning-in-another-insert which sugest this syntax: with rows as ( INSERT INTO Table1 (name) VALUES ('a_title') RETURNING id ) INSERT INTO Table2 (val) SELECT id FROM rows I modified it slightly to look like this: IF _bom_name_key is NULL THEN with rows as ( INSERT INTO project_bom (bom_name) VALUES ('Main') RETURNING project_bom_key ) NEW.project_bom_key = SELECT project_bom_key FROM rows ; But this gives me syntax error. I realize this functionality is slightly different, but can I get the new key into the NEW structure to return from the function call? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
