CODEWITHSUNDEEP
postgresqlfunctiontriggers
Konan Pruiksma
Konan Pruiksma

Reputation: 105

Fill field based on column other table

I have a really simple problem and I am probably overthinking this way too much. But here it goes: I want the fields of a column in one of my tables to be filled automatically whenever I make a new record. The value should be the same (UUID) as the specified (UUID) value from a column in another table. These two columns are joined via a foreign key. So far I have tried making a trigger function but with no results so far:

Create or replace function project_id()
    returns trigger
as $$ begin
if new.project_id is null then
    insert into sporen (project_id)
    select project_id
    from project_info
    where project_code = 'ant0001';
end if;
    return new;
end; 
$$ language plpgsql; 

 CREATE TRIGGER
   project_id_default
 BEFORE update ON
   sporen
 FOR EACH ROW EXECUTE PROCEDURE project_id();

Do I need to specify something as a default in my table? Or am I going about it completely wrong?

Upvotes: 0

Views: 613

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

You only need to assign project_info.project_id to NEW.project_id in your trigger function. No INSERT is needed. Here is an illustration.

Create or replace function project_id() returns trigger as
$$
begin
  if new.project_id is null then
    new.project_id := 
    (
      select pi.project_id 
      from project_info pi
      where pi.project_code = NEW.project_code
    );
  end if;
  return new;
end; 
$$ language plpgsql;

You do not need to specify a default value for project_id in your table.

Upvotes: 1

Related Questions