public inbox for [email protected]
help / color / mirror / Atom feedWriting my first trigger
6+ messages / 4 participants
[nested] [flat]
* Writing my first trigger
@ 2022-05-19 18:18 Chris Tsongas <[email protected]>
0 siblings, 3 replies; 6+ messages in thread
From: Chris Tsongas @ 2022-05-19 18:18 UTC (permalink / raw)
To: [email protected]
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();
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Writing my first trigger
@ 2022-05-19 18:24 Chris Tsongas <[email protected]>
parent: Chris Tsongas <[email protected]>
2 siblings, 1 reply; 6+ messages in thread
From: Chris Tsongas @ 2022-05-19 18:24 UTC (permalink / raw)
To: [email protected]
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 <[email protected]> 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();
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Writing my first trigger
@ 2022-05-19 19:07 Bzzzz <[email protected]>
parent: Chris Tsongas <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Bzzzz @ 2022-05-19 19:07 UTC (permalink / raw)
To: [email protected]
On Thu, 19 May 2022 11:24:41 -0700
Chris Tsongas <[email protected]> wrote:
> 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
> <[email protected]> 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();
You may want to switch to : clock_timestamp() that record the time of
writing (now() stays stuck on the calling time)
> > RETURN NEW;
> > END;
> > $$ LANGUAGE plpgsql;
> >
> > CREATE TRIGGER fullName
> > INSTEAD OF INSERT OR UPDATE ON employee
> > FOR EACH ROW EXECUTE FUNCTION update_employee();
May be something closer to :
CREATE TRIGGER triggername
BEFORE UPDATE ON myschema.mytable
FOR EACH ROW EXECUTE PROCEDURE mytriggerproc
Jean-Yves
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Writing my first trigger
@ 2022-05-20 11:24 hubert depesz lubaczewski <[email protected]>
parent: Chris Tsongas <[email protected]>
2 siblings, 0 replies; 6+ messages in thread
From: hubert depesz lubaczewski @ 2022-05-20 11:24 UTC (permalink / raw)
To: Chris Tsongas <[email protected]>; +Cc: [email protected]
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Writing my first trigger
@ 2022-05-20 15:06 Andreas Kretschmer <[email protected]>
parent: Chris Tsongas <[email protected]>
2 siblings, 1 reply; 6+ messages in thread
From: Andreas Kretschmer @ 2022-05-20 15:06 UTC (permalink / raw)
To: [email protected]
Am 19.05.22 um 20:18 schrieb Chris Tsongas:
> 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();
>
your table doesn't contain the field "updatedA". I would suggest to
calculate the fullName at select-time and not via TRIGGER.
Andreas
--
2ndQuadrant, an EDB company
www.2ndQuadrant.com / www.enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Writing my first trigger
@ 2022-05-21 13:36 Andreas Kretschmer <[email protected]>
parent: Andreas Kretschmer <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Andreas Kretschmer @ 2022-05-21 13:36 UTC (permalink / raw)
To: [email protected]
Am 20.05.22 um 17:06 schrieb Andreas Kretschmer:
>
> your table doesn't contain the field "updatedA". I would suggest to
> calculate the fullName at select-time and not via TRIGGER.
other solution, generated colums:
postgres=# create table employee (firstname text, prefered_firstname
text, lastname text, fullname text generated always as
(coalesce(prefered_firstname,firstname) || ' ' || lastname) stored);
CREATE TABLE
postgres=#
postgres=#
postgres=# insert into employee values ('max',null, 'mueller');
INSERT 0 1
postgres=# insert into employee values ('susann','susi', 'scholz');
INSERT 0 1
postgres=# select * from employee ;
firstname | prefered_firstname | lastname | fullname
-----------+--------------------+----------+-------------
max | | mueller | max mueller
susann | susi | scholz | susi scholz
(2 rows)
postgres=#
Regards, Andreas
--
2ndQuadrant, an EDB company
www.2ndQuadrant.com / www.enterprisedb.com
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2022-05-21 13:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-05-19 18:18 Writing my first trigger Chris Tsongas <[email protected]>
2022-05-19 18:24 ` Chris Tsongas <[email protected]>
2022-05-19 19:07 ` Bzzzz <[email protected]>
2022-05-20 11:24 ` hubert depesz lubaczewski <[email protected]>
2022-05-20 15:06 ` Andreas Kretschmer <[email protected]>
2022-05-21 13:36 ` Andreas Kretschmer <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox