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
ray@rodonnell.ie