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