Re: Affected rows count by rule as condtition

This will deny insert of value that allready exists. Which is ok. But
the second scenerio in which unique constraint refuse operation is, when
u try to update more rows to same value in column with unique constraint.

So i need to use count of affected rows, to deny operation if there are
more then one.

I am using rules as layer to save every version of row in shadow table,
so i cant use unique constraint on column, because of many versions may
have same value.

Robert Haas wrote:
> 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
>