A question about trigger fucntion syntax

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

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