public inbox for [email protected]  

help / color / mirror / Atom feed

From: hubert depesz lubaczewski <[email protected]>
To: Chris Tsongas <[email protected]>
Cc: [email protected]
Subject: Re: Writing my first trigger
Date: Fri, 20 May 2022 13:24:59 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAPhBOiiW8jk_2wcKvCik1eXNejS10qwfGuuovPk0Q=5kvHNxNw@mail.gmail.com>
References: <CAPhBOiiW8jk_2wcKvCik1eXNejS10qwfGuuovPk0Q=5kvHNxNw@mail.gmail.com>

On Thu, May 19, 2022 at 11:18:48AM -0700, Chris Tsongas wrote:
> 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();

I'm not sure instead of trigger will do what you want. The tasks looks
much more like "before" trigger.

The whole if is not really needed. You can use coalesce:

NEW."fullName" := coalesce( NEW.preferredFirstName, NEW.firstName ) || ' ' || NEW.lastName;
RETURN NEW;

and really, really, really, read:
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_table_or_column_names

depesz

view thread (6+ 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]
  Subject: Re: Writing my first trigger
  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