pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedCall 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