From: Ahti Legonkov <lego@127.0.0.1>
Subject: rules / triggers on insert. why after?
Date: Fri, 12 Jul 2002 12:24:01 +0300
Lines: 63
Message-ID: <3D2EA031.3000104@127.0.0.1>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.0.0) Gecko/20020530
To: pgsql-sql@postgresql.org

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