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




В списке pgsql-general по дате отправления:

Предыдущее
От: stan
Дата:
Сообщение: Re: Syntax question about returning value from an insert
Следующее
От: "Lu, Dan"
Дата:
Сообщение: RE: Question on upgrading postgresql from 10.7 to 11.5