[SOLVED] Re: A question about trigger fucntion syntax - Mailing list pgsql-general

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