Re: [GENERAL] [SOLVED] Rules in views, how to?
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 lopes80an...@gmail.com 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. 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 dal...@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!
Re: [GENERAL] [SOLVED] Rules in views, how to?
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
Re: [GENERAL] [SOLVED] Rules in views, how to?
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 s...@samason.me.uk wrote: 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
Re: [GENERAL] [SOLVED] Rules in views, how to?
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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
