Date: Thu, 19 May 2022 21:07:16 +0200
From: Bzzzz <lazyvirus@gmx.com>
To: pgsql-novice@lists.postgresql.org
Subject: Re: Writing my first trigger
Message-ID: <20220519210716.20a02035@msi.defcon1.lan>
In-Reply-To: 
 <CAPhBOijuJPntrK0fcYFWO4fhnLccpvtMffbx_n0rOGu10qvxuQ@mail.gmail.com>
References: 
 <CAPhBOiiW8jk_2wcKvCik1eXNejS10qwfGuuovPk0Q=5kvHNxNw@mail.gmail.com>
	<CAPhBOijuJPntrK0fcYFWO4fhnLccpvtMffbx_n0rOGu10qvxuQ@mail.gmail.com>
Organization: Anyone, anywhere but in banana demokratik republik of france
 or UERSS.
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/20220519210716.20a02035%40msi.defcon1.lan>
Precedence: bulk

On Thu, 19 May 2022 11:24:41 -0700
Chris Tsongas <chris.tsongas@gmail.com> wrote:

> Upon looking at my own code, I realized there's no reason for me to be
> looking at the OLD values, I only care about the NEW:
>
> CREATE OR REPLACE FUNCTION update_employee() RETURNS TRIGGER AS $$
>   BEGIN
>     IF (NEW."preferredFirstName" IS NOT NULL) THEN
>       NEW."fullName" =3D NEW."preferredFirstName" || ' ' ||
> NEW."lastName"; ELSE
>       NEW."fullName" =3D NEW."firstName" || ' ' || NEW."lastName";
>     END IF;
>     NEW."updatedAt" =3D now();
>     RETURN NEW;
>   END;
> $$ LANGUAGE plpgsql;
>
> Any other feedback appreciated!
>
> On Thu, May 19, 2022 at 11:18 AM Chris Tsongas
> <chris.tsongas@gmail.com> wrote:
> >
> > Working on my first trigger to create a fullName value from
> > firstName, optional preferredFirstName, and lastName fields, where
> > the full name uses the optional preferred first name if it exists,
> > otherwise it uses the first name and of course the required last
> > name.
> >
> > Would be great to get feedback on the following code before I try
> > running it (note I already have an employee table, just including
> > the CREATE TABLE statement for clarity):
> >
> > CREATE TABLE employee (
> >   firstName           text NOT NULL,
> >   preferredFirstName  text,
> >   lastName            text NOT NULL,
> >   fullName            text,
> > );
> >
> > CREATE OR REPLACE FUNCTION update_employee() RETURNS TRIGGER AS $$
> >   BEGIN
> >     IF (OLD."preferredFirstName" IS NOT NULL) THEN
> >       NEW."fullName" =3D OLD."preferredFirstName" || ' ' ||
> > OLD."lastName"; ELSE
> >       NEW."fullName" =3D OLD."firstName" || ' ' || OLD."lastName";
> >     END IF;
> >     NEW."updatedAt" =3D now();

You may want to switch to : clock_timestamp() that record the time of
writing (now() stays stuck on the calling time)

> >     RETURN NEW;
> >   END;
> > $$ LANGUAGE plpgsql;
> >
> > CREATE TRIGGER fullName
> > INSTEAD OF INSERT OR UPDATE ON employee
> >     FOR EACH ROW EXECUTE FUNCTION update_employee();

May be something closer to :
CREATE TRIGGER triggername
BEFORE UPDATE ON myschema.mytable
FOR EACH ROW EXECUTE PROCEDURE mytriggerproc

Jean-Yves