On Thu, Dec 26, 2019 at 01:55:34PM +0000, Ray O'Donnell wrote: > On 26/12/2019 13:36, stan wrote: > > IF _bom_name_key is NULL > > THEN > > WITH inserted AS ( > > INSERT into project_bom > > (project_key, bom_name) > > VALUES > > (NEW.project_key , 'Main') > > RETURNING > > project_bom_key > > ) > > /* Syntax error flagged at this line */ > > _bom_name_key = ( SELECT > > project_bom_key > > FROM inserted ) > > ; > > ELSE > > NEW.project_bom_key = _bom_name_key; > > END IF; > > END IF; > > > You need to use the SELECT INTO syntax: > > with inserted as ( > .... > ) > select project_bom_key into _bom_name_key > from inserted > (etc) > > Likewise, while I don't think there's anything wrong with the earlier > assignment - > > _bom_name_key := (select....); > > - to my eye the SELECT INTO looks more natural: > > select project_bom_key > into _bom_name_key > from ... (etc). > > It's a PL/pgSQL construct - full details here: > > https://www.postgresql.org/docs/12/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW > > I hope that this helps. > > Ray. Thanks for the quick response. Yes, that solved my issue. RE different syntax. Yes I need to be more careful to be consistent in the way I do things. I have a tendency to do the same thing different ways when there are multiple ways of doing the same thing. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
