From: "Nicolas Mitchell" <mitchelln@posteo.net>
To: "hubert depesz lubaczewski" <depesz@depesz.com>
Cc: pgsql-novice@lists.postgresql.org
Subject: Re: Trigger function
Date: Tue, 27 Jul 2021 21:05:26 +0000
Message-ID: <2D1DF30A-25BC-48CA-B502-D8B3147B9BEB@posteo.net>
In-Reply-To: <20210727063805.GA28983@depesz.com>
References: <30B8A3D0-8A87-40E2-BE40-31A17BA4B944@posteo.net>
 <20210727063805.GA28983@depesz.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"; format=flowed
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/2D1DF30A-25BC-48CA-B502-D8B3147B9BEB%40posteo.net>
Precedence: bulk

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=E2=80=99ve done a lot of thinking toda=
y.

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:

=3D> 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=E2=80=99m =
too =

green to understand why! I=E2=80=99d 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 <=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=
=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94=E2=80=94> 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:

=3D> 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-lo=
ve-the-triggers/

Thank you, this was really helpful. I expect to be referring back to it =

in the future.

Many thanks,

Nic