MIME-Version: 1.0
References: 
 <CAPhBOiiW8jk_2wcKvCik1eXNejS10qwfGuuovPk0Q=5kvHNxNw@mail.gmail.com>
In-Reply-To: 
 <CAPhBOiiW8jk_2wcKvCik1eXNejS10qwfGuuovPk0Q=5kvHNxNw@mail.gmail.com>
From: Chris Tsongas <chris.tsongas@gmail.com>
Date: Thu, 19 May 2022 11:24:41 -0700
Message-ID: 
 <CAPhBOijuJPntrK0fcYFWO4fhnLccpvtMffbx_n0rOGu10qvxuQ@mail.gmail.com>
Subject: Re: Writing my first trigger
To: pgsql-novice@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAPhBOijuJPntrK0fcYFWO4fhnLccpvtMffbx_n0rOGu10qvxuQ%40mail.gmail.com>
Precedence: bulk

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" = NEW."preferredFirstName" || ' ' || NEW."lastName";
    ELSE
      NEW."fullName" = NEW."firstName" || ' ' || NEW."lastName";
    END IF;
    NEW."updatedAt" = 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" = OLD."preferredFirstName" || ' ' || OLD."lastName";
>     ELSE
>       NEW."fullName" = OLD."firstName" || ' ' || OLD."lastName";
>     END IF;
>     NEW."updatedAt" = now();
>     RETURN NEW;
>   END;
> $$ LANGUAGE plpgsql;
>
> CREATE TRIGGER fullName
> INSTEAD OF INSERT OR UPDATE ON employee
>     FOR EACH ROW EXECUTE FUNCTION update_employee();