From: | stan <stanb(at)panix(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Syntax question about returning value from an insert |
Date: | 2019-12-25 15:26:38 |
Message-ID: | 20191225152638.GA19508@panix.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Rouhaud | 2019-12-25 15:36:24 | Re: Question on upgrading postgresql from 10.7 to 11.5 |
Previous Message | Melvin Davidson | 2019-12-25 15:14:26 | Re: Date created for tables |