Thread: 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 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.
David J.
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
čt 26. 12. 2019 v 18:50 odesílatel stan <stanb@panix.com> napsal:
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
why you use CTE there - it is useless there. INSERT INTO RETURNING should be enough
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