X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 53E718B9CEF for ; Fri, 11 Feb 2005 18:11:24 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 14414-05 for ; Fri, 11 Feb 2005 18:10:55 +0000 (GMT) Received: from mx-00.sil.at (mx-00.sil.at [62.116.68.196]) by svr1.postgresql.org (Postfix) with ESMTP id 272A48B9D13 for ; Fri, 11 Feb 2005 18:10:54 +0000 (GMT) Received: (qmail-ldap/ctrl 90989 invoked from network); 11 Feb 2005 18:10:50 -0000 Received: from unknown (HELO jupiter.sonorys.at) ([213.235.243.42]) (envelope-sender ) by mx-00.sil.at (qmail-ldap-1.03) with SMTP for ; 11 Feb 2005 18:10:50 -0000 Received: from neptun.sonorys.at (neptun.sonorys.at [172.24.1.17]) by jupiter.sonorys.at (8.11.7+Sun/8.11.7) with ESMTP id j1BIBAs05639 for ; Fri, 11 Feb 2005 19:11:10 +0100 (MET) Received: by neptun.sonorys.at with Internet Mail Service (5.5.2653.19) id ; Fri, 11 Feb 2005 19:10:51 +0100 Message-ID: From: =?ISO-8859-1?Q?K=D6PFERL_Robert?= To: Cc: pgsql-sql@postgresql.org Subject: Constraint doesn't see a currently insertet record Date: Fri, 11 Feb 2005 19:10:50 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain; charset="ISO-8859-1" X-Virus-Scanned: by amavisd-new at hub.org X-Archive-Number: 200502/134 X-Sequence-Number: 20245 Hi, yes that's my problem. I've got a table and I put lots of contraints on it so that data stays consistent. One constraint calls a fcn to do some kind of count() over that table but it omits the 'to be inserted record'. What to do? concrete problem: Suppose a table i | tel | status | ... ..| \d+ | 1-7 | ... status can be active, deleted or some more values There may be not two rows where tel is equal and status is not deleted. Other: As long as status=deleted there may be duplicate tel columns. How would I make that sure? My try was to create a check constraint like ("status" = 7) OR (("status" <> 7) AND (num_of_equal_tel_status_not_7("tel") <= 1)) where num_of_equal_tel_status_not_7 is: SELECT count(*) FROM "this table" WHERE "tel"=$1 AND "status"<>7 Note: status=7 means deleted This works well until two recoreds are inserted which are status<>7. The second gets inserted because the new record is not yet visible. Another Idea was to make a trigger. But BTW how do I access a trigger parameter if my trigger function must not have any parameter?????????? Ideas? Tahks for any