Not my day :-( Another syntax error - Mailing list pgsql-general

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