Triggers on Views in PostgreSQL

Question

I want to create a trigger for my view in PostgreSQL. The idea is that all new data must fulfill a condition to be inserted. But something is wrong here and I can't find the answer in manuals.

CREATE OR REPLACE VIEW My_View AS 
SELECT name, adress, count FROM club, band, country;

CREATE OR REPLACE FUNCTION insert() RETURNS TRIGGER AS $$
BEGIN
    IF(NEW.count > 10) THEN
    INSERT INTO My_View VALUES (NEW.name, NEW.adress, NEW.count);
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert INSTEAD OF INSERT ON My_View
FOR EACH ROW EXECUTE PROCEDURE insert();

What is your postgresql version ? What is the error message you have ? Could you open a sandbox on http://sqlfiddle.com ? — greg, Jan 06 '13 at 20:11
My version is 9.1 And the error is strange, because it looks like almost never ending loop... the message is "stack overflow". And error is in that "INSERT" — Ariel Grabijas, Jan 06 '13 at 20:23

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

5

There is a semicolon ( ; ) missing at the end of the INSERT statement in the function.
insert is a reserved word in the SQL standard and should not be used as trigger or function name. Even if it's allowed in PostgreSQL, it's a very bad idea.
There are no join conditions for the three tables club, band, country in your view definition. This leads to a CROSS JOIN, which can be extremely expensive. If there are 1000 rows in each table, you get 1,000,000,000 combinations. You most definitely do not want that.
Also, you should table-qualify the columns in your view definition to avoid ambiguities.

CREATE OR REPLACE VIEW my_view AS 
SELECT ??.name, ??.address, ??.mycount
FROM club    cl
JOIN band    ba ON ?? = ??
JOIN country co ON ?? = ??;

You need to fill in where I left question marks.

And always add a column definition list to your INSERT statements.
And finally, you do not want to INSERT into the same view again, which would create an endless loop and may be the primary cause of your error.

CREATE OR REPLACE FUNCTION f_insert()
  RETURNS TRIGGER AS
$func$
BEGIN
   IF NEW.mycount > 10 THEN
      INSERT INTO my_view ???? (col1?, col2?, col3?)
      VALUES (NEW.name, NEW.address, NEW.mycount);
   END IF;
END
$func$ LANGUAGE plpgsql;

BTW, you shouldn't use count as identifier either. I use mycountinstead.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 06 '13 at 20:26

Erwin Brandstetter

605,456
145
1,078
1,228

OK i get it, so how should i write that condition? – Ariel Grabijas Jan 06 '13 at 21:08
1

@Noran: If table `band` has a column `country_id` that refers to `country_id` of table `country`, you write: `JOIN country co ON co.country_id = ba.country_id` etc. You really need to understand these basics before you can even begin to mess with `INSTEAD` triggers on views. – Erwin Brandstetter Jan 06 '13 at 21:12
Yes i know this and i understand this. I was thinking about that INSERT. Because the way: INSERT INTO col1, col2, col3 VALUES NEW.col1, NEW.col2, NEW.col3 doesnt work. I got syntax error there. – Ariel Grabijas Jan 06 '13 at 21:14
Huh... I am done, my brain is cooked. I will take a look on this tomorrow. THanks for your patience and help. – Ariel Grabijas Jan 06 '13 at 21:19
1

@Noran You're creating an infinite loop by having the trigger on the view try to insert into the view. Your `DO INSTEAD` trigger must insert into the underlying table(s) that the view `SELECT`s its data from. In this case you'd be inserting into `band`, `country` and/or `club`. – Craig Ringer Jan 07 '13 at 05:30
I get it now, there is nothing better then good help and a break ;) Thanks guys. – Ariel Grabijas Jan 07 '13 at 10:35

Triggers on Views in PostgreSQL

1 Answers1