How to write Rules on a View to allow all actions as in the physical table?

From: Andre Lopes <lopes80andre(at)gmail(dot)com>
To: postgresql Forums <pgsql-general(at)postgresql(dot)org>
Subject: How to write Rules on a View to allow all actions as in the physical table?
Date: 2010-03-26 13:52:01
Message-ID: 18f98e681003260652u7463aa6br7dafef0de4dda93@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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,

Browse pgsql-general by date

  From Date Subject
Next Message Alan McKay 2010-03-26 14:02:20 Re: Does anyone use in ram postgres database?
Previous Message Rajan, Pavithra 2010-03-26 13:47:33 Need help on updating an entire column with a list of values, I have.