public inbox for [email protected]  

help / color / mirror / Atom feed

From: Nicolas Mitchell <[email protected]>
To: David G. Johnston <[email protected]>
Cc: hubert depesz lubaczewski <[email protected]>
Cc: pgsql-novice <[email protected]>
Subject: Re: Trigger function
Date: Wed, 28 Jul 2021 00:41:55 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwY-1SH+2aGbceazDw+MvrBLC1raD951vBA7LivE2QmNzQ@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<CAKFQuwazmQBDYdt+zcw2WV+3bONRjbe7qoHiMnREYj+VRXEXjA@mail.gmail.com>
	<[email protected]>
	<CAKFQuwY-1SH+2aGbceazDw+MvrBLC1raD951vBA7LivE2QmNzQ@mail.gmail.com>

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

> On Tue, Jul 27, 2021 at 4:04 PM Nicolas Mitchell 
> <[email protected]>
> 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

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Trigger function
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox