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