Re: Call a Normal function inside a Trigger Function

[Pavel Stehule]

Hi


ne 16. 4. 2023 v 20:47 odesílatel FOUTE K. Jaurès <jauresfo...@gmail.com>
napsal:

> Can I have an example please? Or a link
>
> On Sun, 16 Apr 2023, 17:08 Pavel Stehule, <pavel.steh...@gmail.com> wrote:
>
>> Hi
>>
>>
>> ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès <jauresfo...@gmail.com>
>> napsal:
>>
>>> Hello,
>>>
>>> Is it possible to call a function inside a trigger function ?
>>> Any idea or link are welcome. Thanks in advance
>>>
>>
>> sure, there is not any limit.
>>
>
CREATE OR REPLACE FUNCTION allow_update(d date)
RETURNS bool AS $$
BEGIN
  RETURN EXTRACT(YEAR FROM d) = EXTRACT(YEAR FROM current_date);
END;
$$ LANGUAGE plpgsql;

-- allow update record only from current year
CREATE OR REPLACE FUNCTION trg_func()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    IF NOT allow_update(NEW.inserted) THEN
      RAISE EXCEPTION 'cannot insert';
     END IF;
    ELSE IF TG_OP = 'UPDATE' THEN
     IF NOT allow_update(NEW.inserted) OR NOT allow_update(OLD.inserted)
THEN
       RAISE EXCEPTION 'cannot update';
     END IF;
   ELSE
     IF NOT allow_update(OLD.inserted) THEN
      RAISE EXCEPTION 'cannot delete';
     END IF;
   END IF;
   RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER AFTER INSERT OR UPDATE OR DELETE ON sometab
FOR EACH ROW EXECUTE FUNCTION trg_func();

Regards

Pavel


p.s. You can do everything in trigger - Postgres is not Oracle where there
were some issues (if my memory serves well). There is only one risk -
possible recursion



>> Regards
>>
>> Pavel
>>
>>
>>>
>>> Best Regards
>>> --
>>> Jaurès FOUTE
>>>
>>