Re: What am I doing wrong here?

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.


-- 
Raymond O'Donnell // Galway // Ireland
[email protected]