Not my day :-( Another syntax error

[stan <stanb@xxxxxxxxx>]

Thanks to the folks that helped me fix my earlier error today.

I seem to be having a bad day. I am getting an error that I cannot
understand, and I would appreciate another set of eyes looking at it.

Here is the error


Processing -> load_task.sql
DELETE 0
ERROR:  column "project_cost_category_key" does not exist
LINE 9:    SELECT  project_cost_category_key 
                   ^
QUERY:  WITH inserted AS (
				INSERT into project_cost_category 
				(category)
			VALUES
				('MISC') 
			RETURNING 
				*
			) 
			SELECT  project_cost_category_key 
				                                    = 
				( SELECT 
				project_cost_category_key 
		  	 FROM 
				inserted )
CONTEXT:  PL/pgSQL function default_cost_category() line 25 at SQL statement


Here is the function declaration:

CREATE FUNCTION default_cost_category()
RETURNS trigger AS $$
DECLARE _cost_category_key numeric;
BEGIN
	/* ZZZZZ */
        if NEW.project_cost_category_key IS NULL 
	THEN
		/* DEBUG  
                RAISE NOTICE 'Called default_cost_category() and NEW.project_cost_category_key is NULL' ;
		*/
		 _cost_category_key =  
		(
			SELECT
				project_cost_category_key
			FROM
				project_cost_category
			WHERE
				category = 'MISC' 
		)
		;
		/* DEBUG  
		RAISE NOTICE '_cost_category_key = %', _cost_category_key ;
		*/
		IF _cost_category_key is NULL
		THEN
			WITH inserted AS (
				INSERT into project_cost_category 
				(category)
			VALUES
				('MISC') 
			RETURNING 
				*
			) 
			SELECT  project_cost_category_key 
				INTO NEW.project_cost_category_key  = 
				( SELECT 
				project_cost_category_key 
		  	 FROM 
				inserted )
			;
		ELSE
        		NEW.project_cost_category_key = _cost_category_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 ;

Here are the 2 tables involved in this:


CREATE TABLE bom_item (
    bom_item_key      	  	integer DEFAULT nextval('ica.bom_item_key_serial')
    PRIMARY KEY ,
    project_cost_category_key	integer NOT NULL ,
    project_key       	  	integer NOT NULL ,
    project_bom_key    	  	integer NOT NULL ,
    mfg_part_key      	  	integer NOT NULL ,
    qty               	  	NUMERIC(9,2) NOT NULL ,
    costing_unit_key  	  	integer NOT NULL,
    bom_item          	  	NUMERIC(5,0) ,
/*
	SDB

    I have removed the NOT NULL constraint on this
    for the moment.
    I am thinking that this will not be determined till a purchase 
    order has been issued, and thus it probably
    needs to get populated from the PO creation
    process via a trigger
    cost_per_unit     	  	NUMERIC(6,2) NOT NULL ,
*/
    cost_per_unit     	  	NUMERIC(6,2) ,
    po_terms_and_conditions_key integer ,
    need_date         	  	date ,
    order_date        	  	date ,
    received_date     	  	date ,
    po_no             	  	varchar ,
    po_line_item      	  	NUMERIC(4,0) ,
    po_revision        	  	varchar DEFAULT 0,
    po_rev_date                 date ,
    descrip           	  	varchar ,
    modtime           	  	timestamptz NOT NULL DEFAULT current_timestamp ,
    FOREIGN KEY (project_key) references project(project_key) ON DELETE RESTRICT ,
    FOREIGN KEY (project_bom_key) references project_bom(project_bom_key) ON DELETE RESTRICT ,
    FOREIGN KEY (mfg_part_key) references mfg_part(mfg_part_key)  ON DELETE RESTRICT ,
    FOREIGN KEY (costing_unit_key) references costing_unit(costing_unit_key) ON DELETE RESTRICT ,
    FOREIGN KEY (po_terms_and_conditions_key)
                 references po_terms_and_conditions(po_terms_and_conditions_key) ON DELETE RESTRICT ,
    FOREIGN KEY (project_cost_category_key)
                 references project_cost_category(project_cost_category_key) ON DELETE RESTRICT ,
    CONSTRAINT bom_constraint UNIQUE (bom_item , project_key)
);

CREATE TABLE task_instance (
    task_instance      		integer DEFAULT nextval('ica.task_instance_key_serial')
    PRIMARY KEY ,
    project_key        		integer NOT NULL ,
    project_cost_category_key	integer NOT NULL ,
    employee_key       		integer NOT NULL ,
    work_type_key      		integer NOT NULL ,
    hours 	       		numeric (5, 2) NOT NULL ,
    work_start         		timestamptz NOT NULL NOT NULL ,
    work_end           		timestamptz NOT NULL NOT NULL ,
    modtime            		timestamptz NOT NULL DEFAULT current_timestamp ,
    lock               		boolean DEFAULT TRUE ,
    descrip            		varchar ,
    FOREIGN KEY (employee_key) references employee(employee_key) ON DELETE RESTRICT ,
    FOREIGN KEY (project_key) references project(project_key) ON DELETE RESTRICT ,
    FOREIGN KEY (project_cost_category_key) references project_cost_category(project_cost_category_key) ON DELETE RESTRICT ,
    FOREIGN KEY (work_type_key) references work_type(work_type_key) ON DELETE RESTRICT ,
    FOREIGN KEY (work_type_key , 
		employee_key ,
		project_key ) 
		REFERENCES rate (work_type_key ,
		       	employee_key ,
			project_key ) 
		MATCH FULL  ON DELETE RESTRICT ,
    CONSTRAINT task_constraint UNIQUE (
			employee_key , 
			work_type_key ,
			project_key ,
			work_start ,
			work_end
		)
);
And here is the trigger that is being fried

CREATE TRIGGER default_cost_category_task_trig BEFORE INSERT OR UPDATE ON task_instance
    FOR EACH ROW EXECUTE FUNCTION ica.default_cost_category();


I realize this is a LOT of stuff to look at. I have been staring at this
for quite a while now. As far as I can see the column that the error is
returning is in the CTE, task_instance, AND project_cost_category tables,
So I fail to see why this error is being returned.

Which of the 3 locations of this column is the error related to?

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