Re: What am I doing wrong here?

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