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
