Thread: A question about trigger fucntion syntax
Good morning (at least is is morning East Coast USA time). I am trying to create a function to validate an attempted record insert, and I am having a hard time coming up with syntax that is acceptable. Here is the scenario I have a table that has (among other items) employee_key and work_type_key (both integer FOREIGN KEYS). Then I have another table that has the following structure: CREATE TABLE permitted_work ( employee_key integer , work_type_key integer , permit boolean DEFAULT FALSE NOT NULL , modtime timestamptz DEFAULT current_timestamp , FOREIGN KEY (employee_key) references employee(employee_key) , FOREIGN KEY (work_type_key) references work_type(work_type_key) , CONSTRAINT permit_constraint UNIQUE (employee_key , work_type_key) ); What I think I need to do is create a function that is fired on an insert, or update to the 1st table that verifies that there is an existing row in permitted_work that matches the combination of employee_key AND work_type_key AND has the value TRUE in the permit column. First does this seem to be a good way to achieve this constraint? If not, I am open to suggestions as to other ways to address this requirement. If it does, could someone give me a little help with th syntax of the needed function ?? Thanks for your time helping me with this. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
On Sun, Aug 11, 2019 at 08:56:13AM -0400, stan wrote: > Good morning (at least is is morning East Coast USA time). > > I am trying to create a function to validate an attempted record > insert, and I am having a hard time coming up with syntax that > is acceptable. > > Here is the scenario I have a table that has (among other items) employee_key > and work_type_key (both integer FOREIGN KEYS). Then I have another table > that has the following structure: > > CREATE TABLE permitted_work ( > employee_key integer , > work_type_key integer , > permit boolean DEFAULT FALSE NOT NULL , > modtime timestamptz DEFAULT current_timestamp , > FOREIGN KEY (employee_key) references > employee(employee_key) , > FOREIGN KEY (work_type_key) references > work_type(work_type_key) , > CONSTRAINT permit_constraint UNIQUE > (employee_key , work_type_key) > ); > > What I think I need to do is create a function that is fired on an insert, > or update to the 1st table that verifies that there is an existing row in > permitted_work that matches the combination of employee_key AND > work_type_key AND has the value TRUE in the permit column. > > First does this seem to be a good way to achieve this constraint? If not, > I am open to suggestions as to other ways to address this requirement. > > If it does, could someone give me a little help with th syntax of the > needed function ?? > > Thanks for your time helping me with this. BTW, here is what I Ave tried. CREATE OR REPLACE FUNCTION check_permission() RETURNS trigger AS $BODY$ BEGIN SELECT permit FROM permitted_work WHERE NEW.employee_key = OLD.employee_key AND NEW.work_type_key = OLD.work_type_key RETURN permit; END; $BODY$ LANGUAGE PLPGSQL; and when I try to insert it I get a syntax error at the RETURN -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
Hi
BTW, here is what I Ave tried.
CREATE OR REPLACE FUNCTION check_permission()
RETURNS trigger AS
$BODY$
BEGIN
SELECT
permit
FROM
permitted_work
WHERE
NEW.employee_key = OLD.employee_key
AND
NEW.work_type_key = OLD.work_type_key
RETURN permit;
END;
$BODY$
LANGUAGE PLPGSQL;
and when I try to insert it I get a syntax error at the RETURN
there is more than one issue
1) trigger function should to returns record type (with same type like table joined with trigger). Column permit is a boolean, so some is wrong.
2) the structure of your function is little bit strange. Probably you want some like
CREATE OR REPLACE FUNCTION check_permission()
RETURNS trigger AS $$
DECLARE _permit boolean; -- variables should be declared;
BEGIN
SELECT permit INTO _permit -- result should be assigned to variable
FROM permitted_work
...;
IF NOT permit THEN
RAISE EXCEPTION 'some error message';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Regards
Pavel
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
I trimmed this thread quite a bit. Thanks to the help I have received, I am making progress. I have looked a the recommended documentation, and I believe I am close. I have utilized the "debugging printf" capability here. Here is where I am. I have the following function create. DROP TRIGGER validate_task_trig ON task_instance ; DROP FUNCTION check_permission() ; CREATE FUNCTION check_permission() RETURNS trigger AS $$ DECLARE _permit boolean; BEGIN SELECT permit INTO _permit FROM permitted_work WHERE NEW.employee_key = OLD.employee_key AND NEW.work_type_key = OLD.work_type_key; RAISE NOTICE 'New employee_id % NEW.work_type_key % _permit = %', NEW.employee_key , NEW.work_type_key , _permit ; if NOT _permit THEN RAISE NOTICE 'No permission record'; RAISE EXCEPTION 'No permission record'; ELSE RAISE NOTICE 'Found Permission Record'; END IF; if _permit = FALSE THEN RAISE NOTICE 'Permission Denied'; ELSE RAISE NOTICE 'Permission Granted'; END IF; return NEW; END; $$ LANGUAGE PLPGSQL; CREATE TRIGGER validate_task_trig BEFORE INSERT OR UPDATE ON task_instance FOR EACH ROW EXECUTE FUNCTION check_permission(); Now the issues, currently seems to be that nothing is getting assigned to _permit. Here is the output of a run with 0 records in the permitted_work table. NOTICE: New employee_id 1 NEW.work_type_key 8 _permit = <NULL> NOTICE: Found Permission Record NOTICE: Permission Granted INSERT 0 1 so it appears that nothing is getting assigned to _permit. Also should I be checking for _permit as NOT NULL in the first if clause? Thanks for all the had holding on this. Brand new application for me. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
On Sun, Aug 11, 2019 at 05:31:13PM -0400, stan wrote: > I trimmed this thread quite a bit. > > Thanks to the help I have received, I am making progress. I have looked a the > recommended documentation, and I believe I am close. I have utilized the > "debugging printf" capability here. Here is where I am. I have the following > function create. > > > DROP TRIGGER validate_task_trig ON task_instance ; > > DROP FUNCTION check_permission() ; > > > CREATE FUNCTION check_permission() > RETURNS trigger AS $$ > DECLARE _permit boolean; > BEGIN > SELECT > permit INTO _permit > FROM > permitted_work > WHERE > NEW.employee_key = OLD.employee_key > AND > NEW.work_type_key = OLD.work_type_key; > RAISE NOTICE 'New employee_id % NEW.work_type_key % _permit = %', > NEW.employee_key , > NEW.work_type_key , > _permit ; > if NOT _permit THEN > RAISE NOTICE 'No permission record'; > RAISE EXCEPTION 'No permission record'; > ELSE > RAISE NOTICE 'Found Permission Record'; > END IF; > if _permit = FALSE THEN > RAISE NOTICE 'Permission Denied'; > ELSE > RAISE NOTICE 'Permission Granted'; > END IF; > > return NEW; > END; > $$ > LANGUAGE PLPGSQL; > > CREATE TRIGGER validate_task_trig BEFORE INSERT OR UPDATE ON task_instance > FOR EACH ROW EXECUTE FUNCTION check_permission(); > > > Now the issues, currently seems to be that nothing is getting assigned to > _permit. Here is the output of a run with 0 records in the permitted_work > table. > > NOTICE: New employee_id 1 NEW.work_type_key 8 _permit = <NULL> > NOTICE: Found Permission Record > NOTICE: Permission Granted > INSERT 0 1 > > so it appears that nothing is getting assigned to _permit. Also should I be > checking for _permit as NOT NULL in the first if clause? > > Thanks for all the had holding on this. Brand new application for me. For the archive. I have this working, Here is the function that I woulnd up with. DROP TRIGGER validate_task_trig ON task_instance ; DROP FUNCTION check_permission() ; CREATE FUNCTION check_permission() RETURNS trigger AS $$ DECLARE _permit boolean; BEGIN SELECT permit INTO _permit FROM permitted_work WHERE NEW.employee_key = permitted_work.employee_key AND NEW.work_type_key = permitted_work.work_type_key; if _permit IS NULL THEN RAISE EXCEPTION 'No permission record'; ELSE END IF; if _permit = FALSE THEN RAISE EXCEPTION 'Permisson Denied'; END IF; return NEW; END; $$ LANGUAGE PLPGSQL; CREATE TRIGGER validate_task_trig BEFORE INSERT OR UPDATE ON task_instance FOR EACH ROW EXECUTE FUNCTION check_permission(); Thanks to all the people that were instrumental in helping me learn triggers and functions. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin