rules / triggers on insert. why after?
От | Ahti Legonkov |
---|---|
Тема | rules / triggers on insert. why after? |
Дата | |
Msg-id | 3D2EA031.3000104@127.0.0.1 обсуждение исходный текст |
Ответы |
Re: rules / triggers on insert. why after?
|
Список | pgsql-sql |
Hi, I have the following things in my database: CREATE SEQUENCE "REO_ID_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1; CREATE TABLE reo ( "REO_ID" int4 DEFAULT nextval('"REO_ID_seq"'::text) NOT NULL, "TYPE" varchar(64) NOT NULL, CONSTRAINT"REO_ID_reo_ukey" UNIQUE ("REO_ID"), CONSTRAINT reo_pkey PRIMARY KEY ("REO_ID") ); CREATE TABLE lreo ( "CITY" varchar(64), "STREET" varchar(64), "PRICE" int4, "REO_ID" int4 DEFAULT currval('"REO_ID_seq"'::text), CONSTRAINT "REO_ID_land_reo_ukey" UNIQUE ("REO_ID"), CONSTRAINT "fk_LREO_REO_ID" FOREIGNKEY ("REO_ID") REFERENCES reo ("REO_ID") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE ); ---- either this -- the rule won't work in 7.2.x, because it is done AFTER the Insert has -- executed :( CREATE RULE "rule_lreo_INSERT" AS ON INSERT TO lreo DO INSTEAD INSERT INTO reo("TYPE") values ('lreo'); ---- or this -- but why does not this work ??? CREATE FUNCTION insert_lreo() Returns opaque As ' BeginINSERT INTO reo ("TYPE") VALUES (''lreo'');Return Null; End; ' language 'plpgsql'; CREATE TRIGGER on_insert_lreoBEFORE INSERT ON lreo FOR EACH ROWEXECUTE PROCEDURE insert_lreo(); ---- end On postgres 7.1.3 it was OK to do this: INSERT INTO "lreo" ("STREET", "PRICE", "CITY") VALUES ('street', 1234, 'the city'); but on postgres 7.2 and 7.2.1 I get an error that "REO_ID_seq.currval is not yet defined for this session." The trigger should execute *before* the insert, right? Does anyone know why since postgres 7.2 the rules are executed *after* the insert? Hope you can help me:) -- Ahti Legonkov
В списке pgsql-sql по дате отправления: