st 25. 12. 2019 v 16:26 odesílatel stan <stanb@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.
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