title: Postgresql: Check if Value exists in a table through Trigger
body: <p>I am getting acquainted with Triggers in (Postgre)sql. </p>
  
  <p>What I have now is a table Verine (which is teams in german).</p>
  
  <pre><code>Vereine :{[team:string, punkte:int, serie:int]}
  </code></pre>
  
  <p>This is a very very small thing I wrote just to understand how creating tables, sorting stuff and views work and now I'm using it for triggers. Anyway, team is team obviously the name of the team and primary key, punkte means points and serie refers to the division of the team (just so you understand what the different domains mean).</p>
  
  <p><strong>Problem starts here:</strong></p>
  
  <p>So assume I have a team, let's say "Juventus", already in my table "Vereine". If I then want to insert another row/tuple that has the same key "Juventus", instead of wanting two entries for them, what I'd like is to update the values for key "Juventus" (replacing new values with old ones). In the example below I try to do that with points. </p>
  
  
  
  <pre><code>create table vereine(
      team varchar(20) primary key,
      punkte int not null,
      serie int not null
  )
  
  --beispiel was die Aufgabe verlangt
  
  create trigger prevent_redundancy
  before insert on vereine 
  for each row 
  execute procedure update_points() 
  
  create or replace function update_points()
  returns trigger as 
  $BODY$
  begin 
      if (new.team in (old.team)) then 
      update vereine
      set punkte = new.punkte 
      where team = new.team;
      else
      end if;
  end;
  $BODY$
  LANGUAGE plpgsql;
  
  
  
  --Was die aufgabe verlangt ist, dass keine bereits existierende ID eingefügt wird,
  --sondern der entsprechende Modellname dann umgeändert wird 
  
  
  insert into vereine values('JuventusFC', 50, 1);
  insert into vereine values('AS Roma', 30, 1);
  insert into vereine values('ParmaCalcio1913', 25, 1);
  insert into vereine values('Palermo', 37, 2);
  insert into vereine values('Pescara', 32, 2);
  insert into vereine values('Spezia', 26, 2);
  insert into vereine values('Carrarese Calcio', 34, 3);
  insert into vereine values('Virtus Entella', 31, 3);
  insert into vereine values('Juventus U-23', 50, 3);
  
  
  
  
  select * 
  from vereine
  
  insert into vereine values('JuventusFC', 53, 1);
  </code></pre>
  
  
  
  <p>Here are my problems:</p>
  
  <p>First of all: How would I check if a key already exists in the table? In queries, I would use things like case when, where in or just approach the problem differently. Do I need if statements here? In other words, how would you rewrite</p>
  
  <pre><code>if (new.team in (old.team)) then 
  </code></pre>
  
  <p>so that it checks if it already exists in there?</p>
  
  <p>Secondly: This may be related to the first problem: I get this when trying to insert any tuple:</p>
  
  <pre><code>Query execution failed
  
  Reason:
  SQL Error [54001]: ERROR: stack depth limit exceeded
  Hint: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
  Where: SQL statement "insert into vereine values(new.team, new.punkte, new.serie)"
  </code></pre>
  
  <p>How can I fix my code so that it does what I want it to do? </p>
  
  <p>Sorry for being too wordy. I just want to make sure you understand how this is supposed to work. Yes, Ive asked a question before related to the same assignment but this is a whole different problem. Sorry for that as well.</p>