CODEWITHSUNDEEP
sqlpostgresqltriggersstring-concatenationpostgresql-triggers
Jetro Olowole
Jetro Olowole

Reputation: 123

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

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

Upvotes: 2

Views: 2105

Answers (2)

Belayer
Belayer

Reputation: 14936

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.

Upvotes: 1

Sergey Zaykov
Sergey Zaykov

Reputation: 586

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.

Upvotes: -1

Related Questions