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