Writing my first trigger

From: Chris Tsongas <chris(dot)tsongas(at)gmail(dot)com>
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: Writing my first trigger
Date: 2022-05-19 18:18:48
Message-ID: CAPhBOiiW8jk_2wcKvCik1eXNejS10qwfGuuovPk0Q=5kvHNxNw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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();

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Chris Tsongas 2022-05-19 18:24:41 Re: Writing my first trigger
Previous Message Alan Hodgson 2022-05-19 17:05:36 Re: Comparing two URL strings