Postgresql: Check if Value exists in a table through Trigger

Question

I am getting acquainted with Triggers in (Postgre)sql.

What I have now is a table Verine (which is teams in german).

Vereine :{[team:string, punkte:int, serie:int]}

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).

Problem starts here:

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.

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

Here are my problems:

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

if (new.team in (old.team)) then

so that it checks if it already exists in there?

Secondly: This may be related to the first problem: I get this when trying to insert any tuple:

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)"

How can I fix my code so that it does what I want it to do?

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.

score 2 · Answer 1 · answered Jan 13 '19 at 04:35

What you basically want is an UPSERT syntax, as shown in this answer using a UNIQUE CONSTRAINT on team column. I would suggest you to use this for your insert operation in the relevant function, instead of a trigger.

If you still insist on using a trigger, you could write something like this.

create or replace function update_points()
returns trigger as 
$BODY$
begin 
    if EXISTS (select 1 FROM vereine WHERE team = new.team ) then 
    update vereine
      set punkte = new.punkte 
    where team = new.team;
    RETURN NULL;
     else
    RETURN NEW;
    end if;
end;
$BODY$
LANGUAGE plpgsql;

The difference between RETURN NULL; and RETURN NEW; in a Trigger returning procedure and a Before Insert Trigger is that RETURN NULL won't execute the triggering statement( i.e the main INSERT operation ), whereas RETURN NEW; continues with the intended INSERT statement execution normally.

Demo

Now, coming to your error SQL Error [54001]: ERROR: stack depth limit exceeded, it appears that you have another trigger already created on the table that is triggered by the update operation or you have written another insert and returning NEW from the Trigger. You have to take a call on how to handle if it's another trigger( either to drop it or modify it, but that's beyond the scope of this question, and you should ask it separately if you have further issues)

You can check the existence of Triggers on the table by simply querying the information_schema.triggers or pg_trigger , Refer to this answer for more details.

Postgresql: Check if Value exists in a table through Trigger

1 Answers1