Thread: 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.orszag@gmail.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.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
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.orszag@gmail.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? > >
On Mon, Apr 13, 2009 at 12:59 PM, mito <milos.orszag@gmail.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
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.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 >