Обсуждение: How to write Rules on a View to allow all actions as in the physical table?
Hi,
I have one problem with a view and his rules.
Ok, I have a table to store Session data, the structure is this:
[code]
CREATE TABLE "cti_sessions" (
"session_id" varchar(40) NOT NULL DEFAULT 0,
"ip_address" varchar(16) NOT NULL DEFAULT 0,
"user_agent" varchar(50) NOT NULL,
"last_activity" int4 NOT NULL DEFAULT 0,
"user_data" text,
"coment" varchar(2000),
"id_utiliz_ins" varchar(45),
"id_utiliz_upd" varchar(45),
"data_ult_actual" timestamp,
PRIMARY KEY("session_id"),
CONSTRAINT "ckeck_last_activity" CHECK(last_activity >= 0)
);
[/code]
And I have a view with this structure:
[code]
CREATE OR REPLACE VIEW "ci_sessions" AS
select session_id, ip_address, user_agent, last_activity, user_data from cti_sessions;
CREATE OR REPLACE RULE "ins_ci_sessions" AS
ON INSERT TO "ci_sessions"
DO INSTEAD
(insert into cti_sessions (session_id, ip_address, user_agent, last_activity, user_data) values (new.session_id, new.ip_address, new.user_agent, new.last_activity, new.user_data));
CREATE OR REPLACE RULE "del_ci_sessions" AS
ON DELETE TO "ci_sessions"
DO INSTEAD
(delete from cti_sessions where session_id = old.session_id);
CREATE OR REPLACE RULE "upd_ci_sessions" AS
ON UPDATE TO "ci_sessions"
DO INSTEAD
(update cti_sessions set ip_address = new.ip_address, user_agent = new.user_agent, last_activity = new.last_activity, user_data = new.user_data where session_id = old.session_id);
[/code]
If I use the physical table do to the operations with sessions it works OK. If I use the view it won't works.
How can I write the Rules to allow do all as in the physical table?
Best Regards,
I have one problem with a view and his rules.
Ok, I have a table to store Session data, the structure is this:
[code]
CREATE TABLE "cti_sessions" (
"session_id" varchar(40) NOT NULL DEFAULT 0,
"ip_address" varchar(16) NOT NULL DEFAULT 0,
"user_agent" varchar(50) NOT NULL,
"last_activity" int4 NOT NULL DEFAULT 0,
"user_data" text,
"coment" varchar(2000),
"id_utiliz_ins" varchar(45),
"id_utiliz_upd" varchar(45),
"data_ult_actual" timestamp,
PRIMARY KEY("session_id"),
CONSTRAINT "ckeck_last_activity" CHECK(last_activity >= 0)
);
[/code]
And I have a view with this structure:
[code]
CREATE OR REPLACE VIEW "ci_sessions" AS
select session_id, ip_address, user_agent, last_activity, user_data from cti_sessions;
CREATE OR REPLACE RULE "ins_ci_sessions" AS
ON INSERT TO "ci_sessions"
DO INSTEAD
(insert into cti_sessions (session_id, ip_address, user_agent, last_activity, user_data) values (new.session_id, new.ip_address, new.user_agent, new.last_activity, new.user_data));
CREATE OR REPLACE RULE "del_ci_sessions" AS
ON DELETE TO "ci_sessions"
DO INSTEAD
(delete from cti_sessions where session_id = old.session_id);
CREATE OR REPLACE RULE "upd_ci_sessions" AS
ON UPDATE TO "ci_sessions"
DO INSTEAD
(update cti_sessions set ip_address = new.ip_address, user_agent = new.user_agent, last_activity = new.last_activity, user_data = new.user_data where session_id = old.session_id);
[/code]
If I use the physical table do to the operations with sessions it works OK. If I use the view it won't works.
How can I write the Rules to allow do all as in the physical table?
Best Regards,