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" FOREIGN KEY ("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 '
Begin
INSERT INTO reo ("TYPE") VALUES (''lreo'');
Return Null;
End;
' language 'plpgsql';
CREATE TRIGGER on_insert_lreo
BEFORE INSERT ON lreo FOR EACH ROW
EXECUTE 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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
