From: | Chris Tsongas <chris(dot)tsongas(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)lists(dot)postgresql(dot)org |
Subject: | Re: Writing my first trigger |
Date: | 2022-05-19 18:24:41 |
Message-ID: | CAPhBOijuJPntrK0fcYFWO4fhnLccpvtMffbx_n0rOGu10qvxuQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(dot)tsongas(at)gmail(dot)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();
From | Date | Subject | |
---|---|---|---|
Next Message | Bzzzz | 2022-05-19 19:07:16 | Re: Writing my first trigger |
Previous Message | Chris Tsongas | 2022-05-19 18:18:48 | Writing my first trigger |