Re: Writing my first trigger

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