Thread: What am I doing wrong here?

I am trying to create a function that checks to see if a value exists in a
table, if it does it returns the key, and fills in a NULL filed in an
INSERT. If the value DOES NOT exist, I want the function to insert the needed record it into
the 2nd table, then get the resultant key, and fill in the appropriate
filed in the NEW. structure, so the original insert can proceed.

I am using CTE to return the key created by the INSERT in the 2nd table, but
I must have a syntax error that I do not understand here is the function:

/* 
Function to check if the Main record is in the project_bom table for the 
project, when a BOM name is not supplied by the suer.

If this record exists, return the key associated with it, put this in the
NEW.NEW.project_bom_key field and allow the insert to continue.

If it does not exist, insert it, then populate the NEW.NEW.project_bom_key
with the auto created new key for this record, and allow the INSERT to
continue.
*/

CREATE FUNCTION auto_create_main_bom()
RETURNS trigger AS $$
DECLARE _bom_name_key numeric;
BEGIN
        if NEW.project_bom_key IS NULL 
    THEN
        /* DEBUG
                RAISE NOTICE BOM name is NULL' ;
        */
         _bom_name_key =  
        (
            SELECT
                project_bom_key
            FROM
                project_bom
            WHERE
                project_key = NEW.project_key
            AND
                bom_name = 'Main' 
        )
        ;
        /* DEBUG  
        RAISE NOTICE '_bom_name_key = %', _bom_name_key ;
        */
        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;

        return NEW;
END;
$$ 
LANGUAGE PLPGSQL
SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = ica, "user" , public
VOLATILE ;

When I try to insert this function I get an error on the following line:

_bom_name_key  = ( SELECT

I actually pretty much get a syntax error whatever is at this line.

Can someone please explain what I am doing wrong?

Thanks.

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

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

stan <stanb@panix.com> writes:
> When I try to insert this function I get an error on the following line:
> _bom_name_key  = ( SELECT
> I actually pretty much get a syntax error whatever is at this line.
> Can someone please explain what I am doing wrong?

I think you're misunderstanding where to put the WITH.  That's just
part of the SQL statement as far as plpgsql is concerned, so what
you need to do to use a WITH clause in a variable assignment is
to write

    variable := WITH ... SELECT ... ;

(At least, I think that will work; what you're doing definitely
won't.)

            regards, tom lane

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

On Thu, Dec 26, 2019 at 07:34:28PM +0530, Jayadevan M wrote:
> Hi,
> 
> 
> On Thu, Dec 26, 2019 at 7:06 PM stan <stanb@panix.com> wrote:
> 
> >
> >
> > _bom_name_key  = ( SELECT
> >                         project_bom_key
> >                    FROM inserted )
> > ;
> >
> 
> Try rewriting
> _bom_name_key  = ( SELECT
>                         project_bom_key
>                    FROM inserted )
> 
> as        SELECT project_bom_key into _bom_name_key
>                    FROM inserted ;

Thanks for the fast response, That did fix my issue,
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On Thu, Dec 26, 2019 at 09:06:03AM -0500, Tom Lane wrote:
> stan <stanb@panix.com> writes:
> > When I try to insert this function I get an error on the following line:
> > _bom_name_key  = ( SELECT
> > I actually pretty much get a syntax error whatever is at this line.
> > Can someone please explain what I am doing wrong?
> 
> I think you're misunderstanding where to put the WITH.  That's just
> part of the SQL statement as far as plpgsql is concerned, so what
> you need to do to use a WITH clause in a variable assignment is
> to write
> 
>     variable := WITH ... SELECT ... ;
> 
> (At least, I think that will work; what you're doing definitely
> won't.)

Thanks, that was my mistake.
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin