I have an INSTEAD OF trigger, but PostgreSQL still complains while I insert into view

Question

I have created a view and an INSTEAD OF trigger for inserting/ updating/ deleting from that view. Now I have tried to insert some data to the view, and PostgreSQL returns following error (translation from Czech: "chyba" = "error", "Stav SQL" = "SQL state"):

ERROR: cannot insert into view "ukaz_lok"
DETAIL: Views that return columns that are not columns of their base relation are not automatically updatable.
HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.
********** Chyba **********
ERROR: cannot insert into view "ukaz_lok"
Stav SQL: 55000

This happened before with some other INSTEAD OF triggers - this bug lasted just for some time (few hours? I don't remember exactly) and then PostgreSQL found the trigger. I tried various things (change the trigger, delete all triggers and views and creating them anew etc.), but I'm not sure what worked, just that something worked. So how exactly to make the PostgreSQL recognize the trigger?

I have PostgreSQL 9.3.4 and pgAdmin III 1.18.1 on Windows XP 32 bit. Just in case it was caused by something in the code, here it is:

CREATE TABLE lokalita
(
 kod_lok text NOT NULL,
 nazev text NOT NULL,
 katastr text NOT NULL,
 presnost integer NOT NULL,
 stred geometry(Point,4326) NOT NULL,
 rozsah geometry(Polygon,4326),
 CONSTRAINT pk_lok PRIMARY KEY (kod_lok),
 CONSTRAINT fk_pro_pr FOREIGN KEY (presnost)
 REFERENCES pro_presnost (id) MATCH SIMPLE
 ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
 OIDS=FALSE
);
CREATE OR REPLACE VIEW ukaz_lok AS
 SELECT
 l.kod_lok AS _kod,
 l.nazev AS _nazev,
 l.katastr AS _katastr,
 l.presnost AS _presnost,
 50.0 AS _sirka,
 14.0 AS _delka
 FROM lokalita l;
CREATE OR REPLACE FUNCTION osetri_lok() RETURNS trigger AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
 INSERT INTO lokalita (kod_lok, nazev, katastr, presnost, stred)
 VALUES(
 NEW._kod,
 NEW._nazev,
 NEW._katastr,
 NEW._presnost,
 ST_GeomFromText('POINT('|| NEW.delka ||' '|| NEW.sirka ||')', 4326)
 );
 ELSIF TG_OP = 'UPDATE' THEN
 UPDATE lokalita 
 SET
 nazev = NEW._nazev,
 katastr = NEW._katastr,
 presnost = NEW._presnost,
 stred = ST_GeomFromText('POINT('|| NEW.delka ||' '|| NEW.sirka ||')', 4326)
 WHERE lod_lok = OLD._kod;
 ELSIF TG_OP = 'DELETE' THEN
 DELETE FROM lokalita WHERE kod_lok = OLD._kod;
 END IF;
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER osetri_lok
INSTEAD OF DELETE ON ukaz_lok
FOR EACH ROW
 EXECUTE PROCEDURE osetri_lok();
INSERT INTO ukaz_lok (_kod, _nazev, _katastr, _presnost, _delka, _sirka)
 VALUES(
 'Kote2',
 'Kotěhůlky 2',
 'Kotěhůlky',
 2,
 14.060316,
 50.432044
);

• Erwin Brandstetter: Hint: to get English error messages in your session, run SET lc_messages='C'.

Answer 1

You have a trigger INSTEAD OF DELETE in your code. You also need one on INSTEAD OF INSERT.

You can make that a trigger INSTEAD OF INSERT OR DELETE, but I'd suggest separate trigger functions for INSERT and DELETE and separate triggers. Simplifies the function code (no more need for IF TG_OP = etc.).

Also consider this quote from the manual:

A row-level INSTEAD OF trigger should either return NULL to indicate that it did not modify any data from the view's underlying base tables, or it should return the view row that was passed in (the NEW row for INSERT and UPDATE operations, or the OLD row for DELETE operations). A nonnull return value is used to signal that the trigger performed the necessary data modifications in the view. This will cause the count of the number of rows affected by the command to be incremented. For INSERT and UPDATE operations only, the trigger may modify the NEW row before returning it. This will change the data returned by INSERT RETURNING or UPDATE RETURNING, and is useful when the view will not show exactly the same data that was provided.