Rules in views, how to?
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]
On 6 Jul 2010, at 12:28, Andre Lopes wrote:
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?
In the WHERE-clause you use the columns from the OLD record that uniquely identify that record.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4c330b82286211968020069!
Hi Alban,
But in my application I have more than one way of uniquely identify the
record. Could be by the email field or by the id field.
Thera are update that are done by the WHERE email clause and other by the
WHERE id clause.
It is possible to deal with this?
Best Regards,
On Tue, Jul 6, 2010 at 11:54 AM, Alban Hertroys <
dalroi@solfertje.student.utwente.nl> wrote:
Show quoted text
On 6 Jul 2010, at 12:28, Andre Lopes wrote:
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?
In the WHERE-clause you use the columns from the OLD record that uniquely
identify that record.Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.!DSPAM:921,4c330b7e286211912975436!
Ok, I have done the UPDATE RULE like this and works!
[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
(id = OLD.id or username = OLD.username or email = OLD.email)
[/code]
Best Regards,
On Tue, Jul 6, 2010 at 12:03 PM, Andre Lopes <lopes80andre@gmail.com> wrote:
Show quoted text
Hi Alban,
But in my application I have more than one way of uniquely identify the
record. Could be by the email field or by the id field.Thera are update that are done by the WHERE email clause and other by the
WHERE id clause.It is possible to deal with this?
Best Regards,
On Tue, Jul 6, 2010 at 11:54 AM, Alban Hertroys <
dalroi@solfertje.student.utwente.nl> wrote:On 6 Jul 2010, at 12:28, Andre Lopes wrote:
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?
In the WHERE-clause you use the columns from the OLD record that uniquely
identify that record.Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.!DSPAM:921,4c330b7e286211912975436!
On Tue, Jul 06, 2010 at 12:28:35PM +0100, Andre Lopes wrote:
Ok, I have done the UPDATE RULE like this and works!
where
(id = OLD.id or username = OLD.username or email = OLD.email)
I'm pretty sure you just want to be using the id column above. Using an
OR expression as you're doing could have some strange side effects.
You may also want to consider a UNIQUE constraint on the username (and
maybe email) fields as well, especially as you've said they should be
able to be used to uniquely determine a user.
--
Sam http://samason.me.uk/
Hi,
Thanks for the reply.
In the application there are two kinds of UPDATES to this table.
[code]
update aau_utilizadores
set group_id = 3
where email = pEMAIL;
[/code]
and
[code]
update aau_utilizadores
set password = 3
where id = pNEWPASSWORD;
[/code]
If I use the clause WHERE only in "id" will not work fot both cases, or will
work?
Best Regards,
On Tue, Jul 6, 2010 at 12:46 PM, Sam Mason <sam@samason.me.uk> wrote:
Show quoted text
On Tue, Jul 06, 2010 at 12:28:35PM +0100, Andre Lopes wrote:
Ok, I have done the UPDATE RULE like this and works!
where
(id = OLD.id or username = OLD.username or email = OLD.email)I'm pretty sure you just want to be using the id column above. Using an
OR expression as you're doing could have some strange side effects.You may also want to consider a UNIQUE constraint on the username (and
maybe email) fields as well, especially as you've said they should be
able to be used to uniquely determine a user.--
Sam http://samason.me.uk/--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Tue, Jul 06, 2010 at 12:55:22PM +0100, Andre Lopes wrote:
update aau_utilizadores
set group_id = 3
where email = pEMAIL;
[..]
If I use the clause WHERE only in "id" will not work fot both cases, or will
work?
Yes, it'll do the "right thing". OLD always refers to the previous
version of the row and NEW refers to the new version of the row, you can
use as many or few of the columns as you want.
--
Sam http://samason.me.uk/
On 6 Jul 2010, at 13:03, Andre Lopes wrote:
Hi Alban,
But in my application I have more than one way of uniquely identify the record. Could be by the email field or by the id field.
Unique is unique. There is no other record that could possibly be identified by the same unique identifier. It doesn't matter if you can identify the same record using other identifiers that are also unique, it's still a unique identifier. So if you have a unique identifier for your table you can use on the OLD row in the WHERE clause.
For example, if you have a primary key on that table you can use it to identify the records for the update. Whether that PK uses a simple unique index or a composite unique index doesn't matter at all either.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4c33391e286211703874864!