pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Call a Normal function inside a Trigger Function
7+ messages / 4 participants
[nested] [flat]

* Call a Normal function inside a Trigger Function
@ 2023-04-16 14:18  FOUTE K. Jaurès <jauresfoute@gmail.com>
  0 siblings, 2 replies; 7+ messages in thread

From: FOUTE K. Jaurès @ 2023-04-16 14:18 UTC (permalink / raw)
  To: PostgreSQL General <pgsql-general@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Call a Normal function inside a Trigger Function
@ 2023-04-16 15:05  Erik Wienhold <ewie@ewie.name>
  parent: FOUTE K. Jaurès <jauresfoute@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Erik Wienhold @ 2023-04-16 15:05 UTC (permalink / raw)
  To: FOUTE K. Jaurès <jauresfoute@gmail.com>; PostgreSQL General <pgsql-general@lists.postgresql.org>

> 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





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Call a Normal function inside a Trigger Function
@ 2023-04-16 16:07  Pavel Stehule <pavel.stehule@gmail.com>
  parent: FOUTE K. Jaurès <jauresfoute@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Pavel Stehule @ 2023-04-16 16:07 UTC (permalink / raw)
  To: FOUTE K. Jaurès <jauresfoute@gmail.com>; +Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>

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
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Call a Normal function inside a Trigger Function
@ 2023-04-16 18:47  FOUTE K. Jaurès <jauresfoute@gmail.com>
  parent: Pavel Stehule <pavel.stehule@gmail.com>
  0 siblings, 2 replies; 7+ messages in thread

From: FOUTE K. Jaurès @ 2023-04-16 18:47 UTC (permalink / raw)
  To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>

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
>>
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Call a Normal function inside a Trigger Function
@ 2023-04-16 19:13  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: FOUTE K. Jaurès <jauresfoute@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Adrian Klaver @ 2023-04-16 19:13 UTC (permalink / raw)
  To: FOUTE K. Jaurès <jauresfoute@gmail.com>; Pavel Stehule <pavel.stehule@gmail.com>; +Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>

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






^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Call a Normal function inside a Trigger Function
@ 2023-04-16 19:13  Pavel Stehule <pavel.stehule@gmail.com>
  parent: FOUTE K. Jaurès <jauresfoute@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Pavel Stehule @ 2023-04-16 19:13 UTC (permalink / raw)
  To: FOUTE K. Jaurès <jauresfoute@gmail.com>; +Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>

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
>>>
>>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Call a Normal function inside a Trigger Function
@ 2023-04-16 19:22  FOUTE K. Jaurès <jauresfoute@gmail.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 7+ messages in thread

From: FOUTE K. Jaurès @ 2023-04-16 19:22 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; PostgreSQL General <pgsql-general@lists.postgresql.org>

Thanks @Adrian Klaver <adrian.klaver@aklaver.com>
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
>
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2023-04-16 19:22 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-16 14:18 Call a Normal function inside a Trigger Function FOUTE K. Jaurès <jauresfoute@gmail.com>
2023-04-16 15:05 ` Erik Wienhold <ewie@ewie.name>
2023-04-16 16:07 ` Pavel Stehule <pavel.stehule@gmail.com>
2023-04-16 18:47   ` FOUTE K. Jaurès <jauresfoute@gmail.com>
2023-04-16 19:13     ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-16 19:22       ` FOUTE K. Jaurès <jauresfoute@gmail.com>
2023-04-16 19:13     ` Pavel Stehule <pavel.stehule@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox