Обсуждение: Not my day :-( Another syntax error

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

On Thu, Dec 26, 2019 at 10:39:54AM -0700, David G. Johnston wrote:
> You should probably send that reply again using reply-to-all.
> 
> Dave
> 
> 
> On Thu, Dec 26, 2019 at 10:38 AM stan <stanb@panix.com> wrote:
> 
> > On Thu, Dec 26, 2019 at 10:26:49AM -0700, David G. Johnston wrote:
> > > On Thu, Dec 26, 2019 at 9:33 AM stan <stanb@panix.com> wrote:
> > >
> > > >
> > > >                         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 )
> > > >
> > > >
> > > You have two SELECTs.  The "inner" one has a FROM clause attached to it
> > > providing columns from the "inserted" CTE.  The "outer" one doesn't have
> > a
> > > FROM clause and so doesn't have access to columns.  The "outer" SELECT
> > > project_cost_category_key is thus invalid.
> > >
> >
> >                                 INSERT into project_bom
> >                                 (project_key, bom_name)
> >                         VALUES
> >                                 (NEW.project_key , 'Main')
> >                         RETURNING
> >                                 project_bom_key
> >                         )
> >                         SELECT  project_bom_key INTO NEW.project_bom_key
> > = ( SELECT
> >                                 project_bom_key
> >                          FROM inserted )
> >                         ;
> >
> > Which is working, to the best of my knowledge. BTW the oen I am having
> > trouble with originaly had:
> >
> > RETURBING project_cost_category_key
> >
> > Bit I changed that to * during my debuging efforts.
> >
> > Please tell me if I am looking at this worng.
> >
> > And thatnls for looking through my really long post

Turns out, you were correct, changed it to:


DROP FUNCTION default_cost_category() CASCADE;

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 FROM 
                ( SELECT 
                project_cost_category_key 
               FROM 
                inserted ) AS project_cost_category_key
            ;
        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 ;

And all is well.

Thank you!

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