Postgres Pro
Downloads
Home   >   mailing lists

Thread: Call a Normal function inside a Trigger Function

Call a Normal function inside a Trigger Function

From
FOUTE K. Jaurès
Date:
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

Re: Call a Normal function inside a Trigger Function

From
Erik Wienhold
Date:
> 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

Re: Call a Normal function inside a Trigger Function

From
Pavel Stehule
Date:
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

Re: Call a Normal function inside a Trigger Function

From
FOUTE K. Jaurès
Date:
Can I have an example please? Or a link 

On Sun, 16 Apr 2023, 17:08 Pavel Stehule, <pavel.stehule@gmail.com> wrote:
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

Re: Call a Normal function inside a Trigger Function

From
Adrian Klaver
Date:
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

Re: Call a Normal function inside a Trigger Function

From
Pavel Stehule
Date:
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 link 

On Sun, 16 Apr 2023, 17:08 Pavel Stehule, <pavel.stehule@gmail.com> wrote:
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.

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

Re: Call a Normal function inside a Trigger Function

From
FOUTE K. Jaurès
Date:
Thanks @Adrian Klaver 
It's clear for me now.

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