Re: Trigger function

On 28 Jul 2021, at 0:19, David G. Johnston wrote:

> On Tue, Jul 27, 2021 at 4:04 PM Nicolas Mitchell
> <mitchelln(at)posteo(dot)net>
> wrote:
>
>>
>>> When you write a trigger for a table you should be executing
>>> commands
>>> against the same table.
>>
>> I believe this implies that the code creating a new object (INSERT
>> INTO
>> public.object…) row should reside elsewhere.
>>
>>

I worked out a solution to my question (as stated, but with a different
table):

create function func__new_user_object() returns trigger
language plpgsql
as
$$
begin
if NEW.object is NULL then

-- RAISE NOTICE 'No user.object value, generating a new user object';

WITH object_id AS (
INSERT INTO public.object (type)
VALUES (
(
SELECT obtype.id
FROM public.obtype
WHERE obtype.name LIKE 'user'
))
RETURNING id)
SELECT * FROM object_id INTO NEW.object;

end if;
RETURN NEW;
end
$$;

CREATE TRIGGER trig__new_user_object
BEFORE INSERT
ON public."user"
FOR EACH ROW
EXECUTE PROCEDURE public.func__new_user_object();

>
> Personally, I'm generally against placing this kind of data
> construction
> logic inside triggers.

I have been working with a mind to keep as much logic as I can inside
the database/PostgreSQL. I can’t tell from your comment whether you
prefer other mechanisms available within PG to achieve the same, or
prefer to manage these operations in an application. If within PG, then
I’d view that as something I should explore. Otherwise, I’m not
keen, at present, to push things into an application when they can be
achieved within PG - this being my own (fairly uneducated) preference.
As you may have gathered, this is a new area for me and I am interested
to hear opinions - just a few pointers - now I have what seems to be a
working solution to my question.

Nic