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
