Обсуждение: Call a Normal function inside a Trigger Function
Hello,
Is it possible to call a function inside a trigger function ?
Any idea or link are welcome. Thanks in advance
Best Regards
-- Jaurès FOUTE
> On 16/04/2023 16:18 CEST FOUTE K. Jaurès <jauresfoute@gmail.com> wrote:
>
> Is it possible to call a function inside a trigger function ?
> Any idea or link are welcome. Thanks in advance
Depends on what you want to do with the return value. Use PERFORM to ignore
the result. [0] Use SELECT INTO to handle a single-row result. [1]
PERFORM myfunc();
SELECT myfunc() INTO myresult;
[0] https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL
[1] https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW
--
Erik
Hi
ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès <jauresfoute@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.
Regards
Pavel
Best Regards--Jaurès FOUTE
Can I have an example please? Or a link
On Sun, 16 Apr 2023, 17:08 Pavel Stehule, <pavel.stehule@gmail.com> wrote:
Hine 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès <jauresfoute@gmail.com> 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.RegardsPavelBest Regards--Jaurès FOUTE
On 4/16/23 11:47, FOUTE K. Jaurès wrote:
> Can I have an example please? Or a link
create table trg_test (id integer, fld_1 varchar, fld_2 boolean);
CREATE OR REPLACE FUNCTION public.child_fnc(token character varying)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
IF token = 'yes' THEN
RAISE NOTICE 'Child';
END IF;
END;
$function$
;
CREATE OR REPLACE FUNCTION public.parent_fnc()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'Id is %', NEW.id;
RAISE NOTICE 'Fld_1 is %', NEW.fld_1;
RAISE NOTICE 'Parent';
PERFORM child_fnc('yes');
RETURN NEW;
END;
$function$
;
create trigger test_trg before insert on trg_test for each row execute
function parent_fnc();
insert into trg_test values (1, 'dog', 'f');
NOTICE: Id is 1
NOTICE: Fld_1 is dog
NOTICE: Parent
NOTICE: Child
INSERT 0 1
>
> On Sun, 16 Apr 2023, 17:08 Pavel Stehule, <pavel.stehule@gmail.com
> <mailto:pavel.stehule@gmail.com>> wrote:
>
> Hi
>
>
> ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès
> <jauresfoute@gmail.com <mailto:jauresfoute@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.
>
> Regards
>
> Pavel
>
>
> Best Regards
> --
> Jaurès FOUTE
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Hi
ne 16. 4. 2023 v 20:47 odesílatel FOUTE K. Jaurès <jauresfoute@gmail.com> napsal:
Can I have an example please? Or a linkOn Sun, 16 Apr 2023, 17:08 Pavel Stehule, <pavel.stehule@gmail.com> wrote:Hine 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès <jauresfoute@gmail.com> 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
On Sun, 16 Apr 2023, 20:13 Adrian Klaver, <adrian.klaver@aklaver.com> wrote:
On 4/16/23 11:47, FOUTE K. Jaurès wrote:
> Can I have an example please? Or a link
create table trg_test (id integer, fld_1 varchar, fld_2 boolean);
CREATE OR REPLACE FUNCTION public.child_fnc(token character varying)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
IF token = 'yes' THEN
RAISE NOTICE 'Child';
END IF;
END;
$function$
;
CREATE OR REPLACE FUNCTION public.parent_fnc()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'Id is %', NEW.id;
RAISE NOTICE 'Fld_1 is %', NEW.fld_1;
RAISE NOTICE 'Parent';
PERFORM child_fnc('yes');
RETURN NEW;
END;
$function$
;
create trigger test_trg before insert on trg_test for each row execute
function parent_fnc();
insert into trg_test values (1, 'dog', 'f');
NOTICE: Id is 1
NOTICE: Fld_1 is dog
NOTICE: Parent
NOTICE: Child
INSERT 0 1
>
> On Sun, 16 Apr 2023, 17:08 Pavel Stehule, <pavel.stehule@gmail.com
> <mailto:pavel.stehule@gmail.com>> wrote:
>
> Hi
>
>
> ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès
> <jauresfoute@gmail.com <mailto:jauresfoute@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.
>
> Regards
>
> Pavel
>
>
> Best Regards
> --
> Jaurès FOUTE
>
--
Adrian Klaver
adrian.klaver@aklaver.com