Re: Affected rows count by rule as condtition
От | Robert Haas |
---|---|
Тема | Re: Affected rows count by rule as condtition |
Дата | |
Msg-id | 603c8f070904130935p1cf24675o29dea948beb2329@mail.gmail.com обсуждение исходный текст |
Ответ на | Affected rows count by rule as condtition (mito <milos.orszag@gmail.com>) |
Ответы |
Re: Affected rows count by rule as condtition
|
Список | pgsql-hackers |
On Mon, Apr 13, 2009 at 12:12 PM, mito <milos.orszag@gmail.com> wrote: > Hi, > is there any way how to count affected rows by on update rule and use it as > part of condtions. > > Example: > > > CREATE OR REPLACE RULE "_UPDATE" AS ON UPDATE TO "users" DO INSTEAD ( > UPDATE "s_users" SET > id = new.id, > login = new.login, > WHERE id IN (SELECT id FROM "s_users" ) AND 2 > (SELECT count(new.id)) ; > > Error: agregate functions not allowed in WHERE statement > > It need to simulate unique constraint on field s_users.new_id, so it should > deny to update multiple rows with same value. > > Any suggestions are welcome. Well, you could probably make this compile by rewriting the broken part as "SELECT SUM(1) FROM s_users WHERE id = NEW.id", but it won't guarantee uniqueness in the face of concurrent transactions, even if you use SERIALIZABLE mode. There's a reason that unique constraints are built into the database.... you should use them. ...Robert
В списке pgsql-hackers по дате отправления: