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
