Rules in views, how to? - Mailing list pgsql-general
From | Andre Lopes |
---|---|
Subject | Rules in views, how to? |
Date | |
Msg-id | AANLkTimopMT4eyRVMomAiWSBOMJoUD12tMIcXni0dYEq@mail.gmail.com Whole thread Raw |
Responses |
Re: Rules in views, how to?
|
List | pgsql-general |
Hi,
I'am using rules in views, but I'am not sure about how the rules work... let me explain...
For example, I have this table:
[code]
CREATE TABLE "atau_utilizadores" (
"id" int4 NOT NULL,
"group_id" int4 NOT NULL,
"ip_address" char(16) NOT NULL,
"username" varchar(50) NOT NULL,
"password" varchar(40) NOT NULL,
"salt" varchar(40),
"email" varchar(40) NOT NULL,
"activation_code" varchar(40),
"forgotten_password_code" varchar(40),
"remember_code" varchar(40),
"created_on" timestamp NOT NULL,
"last_login" timestamp,
"active" int4,
"coment" varchar(2000),
"id_utiliz_ins" varchar(45),
"id_utiliz_upd" varchar(45),
"data_ult_actual" timestamp,
PRIMARY KEY("id"),
CONSTRAINT "check_id" CHECK(id >= 0),
CONSTRAINT "check_group_id" CHECK(group_id >= 0),
CONSTRAINT "check_active" CHECK(active >= 0)
);
[/code]
And I have also a view to this table with a rule do the user be able to do INSERTS in views:
[code]
CREATE OR REPLACE VIEW "aau_utilizadores" AS
select * from atau_utilizadores;
CREATE OR REPLACE RULE "ins_aau_utilizadores" AS
ON INSERT TO "aau_utilizadores"
DO INSTEAD
(insert into atau_utilizadores
(id, group_id, ip_address, username, password, salt, email, activation_code,
forgotten_password_code, remember_code, created_on, last_login, active)
values (NEW.id, NEW.group_id, NEW.ip_address, NEW.username, NEW.password,
NEW.salt, NEW.email, null, null, null, NEW.created_on, null, NEW.active));
[/code]
Now I need also to add an INSTEAD UPDATE rule and an INSTEAD DELETE rule, but I have some doubts about it... let me explain...
Ok, I need to create an INSTEAD UPDATE rule in this view, but I don't know how to use the clause WHERE in the UPDATE rule. For example the UPDATE could be done when "WHERE email = 'X' " or "WHERE id = 'Y' ".
Question: How can I deal with this?
The update rule should be:
[code]
update atau_utilizadores
set group_id = NEW.group_id,
password = NEW.password,
salt = NEW.salt,
email = NEW.email,
activation_code = NEW.activation_code,
forgotten_password_code = NEW.forgotten_password_code,
remember_code = NEW.remember_code,
created_on = NEW.created_on,
last_login = NEW.last_login,
active = NEW.active
[/code]
or
[code]
update atau_utilizadores
set group_id = NEW.group_id,
password = NEW.password,
salt = NEW.salt,
email = NEW.email,
activation_code = NEW.activation_code,
forgotten_password_code = NEW.forgotten_password_code,
remember_code = NEW.remember_code,
created_on = NEW.created_on,
last_login = NEW.last_login,
active = NEW.active
where
(email = OLD.email or id = OLD.id)
???
PS: Sorry for my bad english.
Best Regards,
André
[/code]
I'am using rules in views, but I'am not sure about how the rules work... let me explain...
For example, I have this table:
[code]
CREATE TABLE "atau_utilizadores" (
"id" int4 NOT NULL,
"group_id" int4 NOT NULL,
"ip_address" char(16) NOT NULL,
"username" varchar(50) NOT NULL,
"password" varchar(40) NOT NULL,
"salt" varchar(40),
"email" varchar(40) NOT NULL,
"activation_code" varchar(40),
"forgotten_password_code" varchar(40),
"remember_code" varchar(40),
"created_on" timestamp NOT NULL,
"last_login" timestamp,
"active" int4,
"coment" varchar(2000),
"id_utiliz_ins" varchar(45),
"id_utiliz_upd" varchar(45),
"data_ult_actual" timestamp,
PRIMARY KEY("id"),
CONSTRAINT "check_id" CHECK(id >= 0),
CONSTRAINT "check_group_id" CHECK(group_id >= 0),
CONSTRAINT "check_active" CHECK(active >= 0)
);
[/code]
And I have also a view to this table with a rule do the user be able to do INSERTS in views:
[code]
CREATE OR REPLACE VIEW "aau_utilizadores" AS
select * from atau_utilizadores;
CREATE OR REPLACE RULE "ins_aau_utilizadores" AS
ON INSERT TO "aau_utilizadores"
DO INSTEAD
(insert into atau_utilizadores
(id, group_id, ip_address, username, password, salt, email, activation_code,
forgotten_password_code, remember_code, created_on, last_login, active)
values (NEW.id, NEW.group_id, NEW.ip_address, NEW.username, NEW.password,
NEW.salt, NEW.email, null, null, null, NEW.created_on, null, NEW.active));
[/code]
Now I need also to add an INSTEAD UPDATE rule and an INSTEAD DELETE rule, but I have some doubts about it... let me explain...
Ok, I need to create an INSTEAD UPDATE rule in this view, but I don't know how to use the clause WHERE in the UPDATE rule. For example the UPDATE could be done when "WHERE email = 'X' " or "WHERE id = 'Y' ".
Question: How can I deal with this?
The update rule should be:
[code]
update atau_utilizadores
set group_id = NEW.group_id,
password = NEW.password,
salt = NEW.salt,
email = NEW.email,
activation_code = NEW.activation_code,
forgotten_password_code = NEW.forgotten_password_code,
remember_code = NEW.remember_code,
created_on = NEW.created_on,
last_login = NEW.last_login,
active = NEW.active
[/code]
or
[code]
update atau_utilizadores
set group_id = NEW.group_id,
password = NEW.password,
salt = NEW.salt,
email = NEW.email,
activation_code = NEW.activation_code,
forgotten_password_code = NEW.forgotten_password_code,
remember_code = NEW.remember_code,
created_on = NEW.created_on,
last_login = NEW.last_login,
active = NEW.active
where
(email = OLD.email or id = OLD.id)
???
PS: Sorry for my bad english.
Best Regards,
André
[/code]
pgsql-general by date: