Hi st 25. 12. 2019 v 16:26 odesÃlatel stan <st...@panix.com> napsal:
> 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. > you example is little bit confused. probably it should to be CREATE OR REPLACE FUNCTION ... RETURNS ... AS $$ BEGIN ... INSERT INTO ... VALUES('...') RETURNING project_bom_key INTO NEW.project_bom_key; You cannot to use plpgsql statements inside SQL statements - you cannot to use assign statement (plpgsql) inside SQL statement (WITH). > I realize this functionality is slightly different, but can I get the new > key into the NEW structure to return from the function call? > The fields of records are fixed in first time of created composite value, and cannot to enhanced in time. But maybe I don't understand well to your use case. Your examples looks chaotic little bit. Regards Pavel > > -- > "They that would give up essential liberty for temporary safety deserve > neither liberty nor safety." > -- Benjamin Franklin > > >
