Re: Affected rows count by rule as condtition

On Mon, Apr 13, 2009 at 12:12 PM, mito <milos(dot)orszag(at)gmail(dot)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