Syntax question about returning value from an insert

Поиск
Список
Период
Сортировка
Искать
От
stan
Тема
Syntax question about returning value from an insert
Дата
Msg-id
20191225152638.GA19508@panix.com
Список
Дерево обсуждения
Syntax question about returning value from an insert stan <stanb@panix.com>
Re: Syntax question about returning value from an insert Adrian Klaver <adrian.klaver@aklaver.com>
Re: Syntax question about returning value from an insert stan <stanb@panix.com>
Re: Syntax question about returning value from an insert Adrian Klaver <adrian.klaver@aklaver.com>
Re: Syntax question about returning value from an insert stan <stanb@panix.com>
Re: Syntax question about returning value from an insert Adrian Klaver <adrian.klaver@aklaver.com>
Re: Syntax question about returning value from an insert stan <stanb@panix.com>
Re: Syntax question about returning value from an insert Rob Sargent <robjsargent@gmail.com>
Re: Syntax question about returning value from an insert Adrian Klaver <adrian.klaver@aklaver.com>
Re: Syntax question about returning value from an insert stan <stanb@panix.com>
Re: Syntax question about returning value from an insert stan <stanb@panix.com>
Re: Syntax question about returning value from an insert Adrian Klaver <adrian.klaver@aklaver.com>
Re: Syntax question about returning value from an insert Rob Sargent <robjsargent@gmail.com>
Re: Syntax question about returning value from an insert Pavel Stehule <pavel.stehule@gmail.com>
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


В списке pgsql-general по дате отправления
От: Melvin Davidson
Дата:
Сообщение: Re: Date created for tables
От: Julien Rouhaud
Дата:
FAQ