Hi
ne 16. 4. 2023 v 20:47 odesílatel FOUTE K. Jaurès <jauresfoute@xxxxxxxxx> napsal:
Can I have an example please? Or a linkOn Sun, 16 Apr 2023, 17:08 Pavel Stehule, <pavel.stehule@xxxxxxxxx> wrote:Hine 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès <jauresfoute@xxxxxxxxx> napsal:Hello,Is it possible to call a function inside a trigger function ?Any idea or link are welcome. Thanks in advancesure, 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
RegardsPavelBest Regards--Jaurès FOUTE
