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
);
SET lc_messages='C'
.