Re: A question about trigger fucntion syntax

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