A question about trigger fucntion syntax

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

A question about trigger fucntion syntax

stan-9
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



Reply | Threaded
Open this post in threaded view
|

Re: A question about trigger fucntion syntax

stan-9
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


Reply | Threaded
Open this post in threaded view
|

Re: A question about trigger fucntion syntax

Pavel Stehule


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


Reply | Threaded
Open this post in threaded view
|

Re: A question about trigger fucntion syntax

stan-9
In reply to this post by stan-9
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


Reply | Threaded
Open this post in threaded view
|

[SOLVED] Re: A question about trigger fucntion syntax

stan-9
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