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