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.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
