How can I concat two strings to update a column in a postgres table using trigger function

Question

I have user table and profile table. When a record is created in user table, it automatically create a record in profile table with the returned id. The profile table is like:

create table app_public.profiles (
  user_id int not null primary key references users,
  first_name       text check (char_length(first_name) < 50),
  last_name        text check (char_length(last_name) < 50),
  display_name text
);

When a user update the profile table and enter values for first_name and last_name, I want a trigger function to concat the two columns and use the result to update display_name.

Currently I define the trigger function as

create function app_public.tg_user_display_name() returns trigger as $$
begin
  NEW.display_name = CONCAT(profiles.first_name || ' ' || NEW.profiles.last_name);
  return NEW;
end;
$$ language plpgsql volatile set search_path from current;

Also I have the trigger as

create trigger _500_update_display_name
  before update on app_public.profiles
  for each row
  execute procedure app_public.tg_user_display_name();

1) Why do this at all? Just do it when you query the table. 2) If you want to do this then it is `NEW.first_name` and `NEW.last_name`. `NEW` only holds values for the table the trigger is on as `NEW.`. — Adrian Klaver, Aug 17 '21 at 17:03
If an answer solved your problem please accept it. Accepting an answer helps later questioners having the same issue. Further it removes the question from the unanswered queue. If none were sufficient to solve your issue please post what remains. — Belayer, Aug 21 '21 at 03:23

score 2 · Answer 1 · answered Aug 17 '21 at 23:28

There is a simpler way. Rather that creating a check constraint to enforce a size check just put that limit on the definition. Further the best way to handle NULL for either name is to just not allow it. so define Profiles as:

create table  profiles (
   user_id      integer     not null primary key  
 , first_name   varchar(49) not null
 , last_name    varchar(49) not null 
 , display_name text  
);

If you have a newer Postgres version (v12 or greater) you can eliminate the the trigger function and the trigger altogether. Define display_name as a generated column. Then just forget about it, except Select there nothing you do with it anyway.

create table  profiles (
   user_id      integer     not null primary key  
 , first_name   varchar(49) not null
 , last_name    varchar(49) not null 
 , display_name text generated always as (first_name || ' ' || last_name) stored 
);

See the difference in the following examples:

Profile with Trigger. Note: This will require the trigger to fire on both Insert and Update. Otherwise an Update will do what you tell it to.

Profile with Generated Column. Note: Insert and Update will do what you wait it do.

the 2nd approach with generated column work perfectly for my use case. I tested it on my Graphql query and it was perfect. Thanks. — Wacademy, Aug 18 '21 at 01:49

Sergey Zaykov · Answer 2 · 2021-08-17T19:33:47.430

0

it is not clear what is wrong. Your tigger function looks like good. A small problem is using CONCAT() function together string concatenation operator.

SELECT 'Jon' || ' ' || 'Dir', concat('Jon' , ' ' , 'Dir')

Use RAISE NOTICE or RAISE EXCEPTION for debugging.

edited Aug 17 '21 at 19:33

answered Aug 17 '21 at 19:22

Sergey Zaykov

523
2
9

How can I concat two strings to update a column in a postgres table using trigger function

2 Answers2