Re: Affected rows count by rule as condtition

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.

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

why not simply create a UNIQUE constraint?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

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

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.

mito

Jaime Casanova wrote:
> On Mon, Apr 13, 2009 at 11:12 AM, mito <milos(dot)orszag(at)gmail(dot)com> wrote:
>> 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.
>>
>
> why not simply create a UNIQUE constraint?
>
>

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
>

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

Use a partial index.

...Robert