X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A19403A3D4E for ; Mon, 3 Jan 2005 17:09:51 +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 35047-06 for ; Mon, 3 Jan 2005 17:09:33 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 8EA443A3D88 for ; Mon, 3 Jan 2005 17:09:17 +0000 (GMT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 03 Jan 2005 11:09:15 -0600 Subject: Re: Rules on a view overwrite default values. Any way to From: Scott Marlowe To: Bradley Kieser Cc: PgSQL ADMIN In-Reply-To: <41D97A9C.6050401@kieser.net> References: <41D97A9C.6050401@kieser.net> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1104772155.23251.10.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Mon, 03 Jan 2005 11:09:15 -0600 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/9 X-Sequence-Number: 15868 On Mon, 2005-01-03 at 11:02, Bradley Kieser wrote: > Hi, > > I use views extensively to implement security on tables. This requires > each table to have a view. All updates, inserts and deletes take place > through the view. The view has rules for each of these operations > defining security for that table. Under other conditions, the view also > removes complex views of the underlying data from the application layer > by supplying a view where the rules for update, insert and delete > implement business logic. > > The problem is that rules on a view mean that the default values for NOT > NULL columns (used extensively) no longer trigger! > > E.g. > > Table A had column "updated_time" which is not null default now(). > Inserting into the view on table A where "updated_time" has not been > supplied will not fill in "now()". It will attempt to put in a null > value and hence the insert will fail in the insert rule on that view. > > Is there any way to tell PG to implement the triggers on the underlying > table? It will make it extremely difficult to implement this schema if I > have to try to put in null field handling... and it really should be PG > doing this not me! No doubt if this is a bug, it will be fixed in 8.x! Try changing the udpate triggers you're creating to pass in DEFAULT (similar to NULL in the way it's NOT quoted, etc...) in the update / insert query to the lower level and see if that fixes things.