| 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: | Whole Thread | Raw Message | 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,
| 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. |