Re: Syntax question about returning value from an insert

On 12/25/19 7:26 AM, stan wrote:
> 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'm with Pavel in not understanding what you want to do. This prevents
any clear discussion on what to do below. To help:

1) Schema of t1 and t2.

2) Default for what column?

3) What is the key?

>
> 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?
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com