On Mon, Apr 13, 2009 at 12:12 PM, mito <milos.ors...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
