From: "Nicolas Mitchell" <mitchelln@posteo.net>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "hubert depesz lubaczewski" <depesz@depesz.com>,
 pgsql-novice <pgsql-novice@lists.postgresql.org>
Subject: Re: Trigger function
Date: Wed, 28 Jul 2021 00:41:55 +0000
Message-ID: <09E00165-C826-44D5-9CD2-E767DFFEC099@posteo.net>
In-Reply-To: 
 <CAKFQuwY-1SH+2aGbceazDw+MvrBLC1raD951vBA7LivE2QmNzQ@mail.gmail.com>
References: <30B8A3D0-8A87-40E2-BE40-31A17BA4B944@posteo.net>
 <20210727063805.GA28983@depesz.com>
 <2D1DF30A-25BC-48CA-B502-D8B3147B9BEB@posteo.net>
 <CAKFQuwazmQBDYdt+zcw2WV+3bONRjbe7qoHiMnREYj+VRXEXjA@mail.gmail.com>
 <53180757-855C-4FFF-B5CC-572BAEFD9397@posteo.net>
 <CAKFQuwY-1SH+2aGbceazDw+MvrBLC1raD951vBA7LivE2QmNzQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/09E00165-C826-44D5-9CD2-E767DFFEC099%40posteo.net>
Precedence: bulk

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

> On Tue, Jul 27, 2021 at 4:04 PM Nicolas Mitchell 
> <mitchelln@posteo.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