Re: Call a Normal function inside a Trigger Function

Hi

ne 16. 4. 2023 v 20:47 odesílatel FOUTE K. Jaurès <jauresfoute(at)gmail(dot)com>
napsal:

> Can I have an example please? Or a link
>
> On Sun, 16 Apr 2023, 17:08 Pavel Stehule, <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>> Hi
>>
>>
>> ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès <jauresfoute(at)gmail(dot)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
>>>
>>