Re: Trigger function - Mailing list pgsql-novice
From | Nicolas Mitchell |
---|---|
Subject | Re: Trigger function |
Date | |
Msg-id | 2D1DF30A-25BC-48CA-B502-D8B3147B9BEB@posteo.net Whole thread Raw |
In response to | Re: Trigger function (hubert depesz lubaczewski <depesz@depesz.com>) |
Responses |
Re: Trigger function
("David G. Johnston" <david.g.johnston@gmail.com>)
|
List | pgsql-novice |
On 27 Jul 2021, at 7:38, hubert depesz lubaczewski wrote: > *after* insert, not *before*. > But other than that yes. Apologies for the lengthy reply; I’ve done a lot of thinking today. I cannot understand why it would be *after* insert: A required column in a new host row (host.object) cannot be populated until a new row is inserted into the object table. Therefore the function must (surely?) be executed prior to an insert (on the host table). From the point of view of a user, the command they would issue is: => INSERT INTO host (name, domain) VALUES ('gary', 1000001); I imagined the trigger/function taking the supplied information (name, domain) and retrieving the value for host.object before insert. But when I have tried this with the following trigger/function (BEFORE/AFTER), PG goes into a loop. The two associated sequences (object, host) are incremented until I break the execution but no insert happens in either table. My code is causing an endless loop. I’m too green to understand why! I’d be grateful for any hints to help me on my way. CREATE OR REPLACE FUNCTION public.func__host__bi() RETURNS trigger AS $$ begin WITH object_id AS (INSERT INTO public.object (type) VALUES ( ( SELECT obtype.id FROM public.obtype WHERE obtype.name LIKE 'host' ) ) RETURNING id) INSERT INTO host (name, domain, object) VALUES (NEW.name, NEW.domain, (SELECT * FROM object_id)); RETURN NEW; end $$ LANGUAGE 'plpgsql' CREATE TRIGGER trig__host_bi BEFORE INSERT <————————————> or AFTER INSERT ON public."host" FOR EACH ROW EXECUTE PROCEDURE public.func__host__bi(); Later, and this is for another day, after reading your article (which mentions view triggers) it occurred to me to try a trigger attached to a view of the hosts table. I created a view and attached a trigger (only INSTEAD OF allowed, noted) calling the same function: CREATE VIEW public.v_host ( id, "name", "domain", "object" ) AS SELECT host.id, host.name, host.domain, host.object FROM host; CREATE TRIGGER trig__v_host__bi INSTEAD OF INSERT ON public.v_host FOR EACH ROW EXECUTE PROCEDURE public.func__host__bi(); CREATE OR REPLACE FUNCTION public.func__host__bi() RETURNS trigger AS $$ begin WITH object_id AS (INSERT INTO public.object (type) VALUES ( ( SELECT obtype.id FROM public.obtype WHERE obtype.name LIKE 'host' ) ) RETURNING id) INSERT INTO host (name, domain, object) VALUES (NEW.name, NEW.domain, (SELECT * FROM object_id)); RETURN NEW; end $$ LANGUAGE 'plpgsql' The previous psql command would now be: => INSERT INTO v__host (name, domain) VALUES ('gary', 1000001); Which succeeds when executed, - inserting a new object (of type host) - inserting a new host, with the object.id (host.object) of the newly created object - incrementing the two sequences by one each > If I might suggest: > https://www.depesz.com/2012/11/14/how-i-learned-to-stop-worrying-and-love-the-triggers/ Thank you, this was really helpful. I expect to be referring back to it in the future. Many thanks, Nic
pgsql-novice by date: