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 DE7DD3A3C5E for ; Mon, 3 Jan 2005 17:16:58 +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 37166-10 for ; Mon, 3 Jan 2005 17:16:41 +0000 (GMT) Received: from laptop.kieser.net (kieser2.demon.co.uk [80.176.229.121]) by svr1.postgresql.org (Postfix) with ESMTP id 2E9D63A3C52 for ; Mon, 3 Jan 2005 17:16:40 +0000 (GMT) Received: from [127.0.0.1] (laptop.kieser2.demon.co.uk [127.0.0.1]) by laptop.kieser.net (Postfix) with ESMTP id A67BF18330; Mon, 3 Jan 2005 17:17:02 +0000 (GMT) Message-ID: <41D97E0D.1030202@kieser.net> Date: Mon, 03 Jan 2005 17:17:01 +0000 From: Bradley Kieser User-Agent: Mozilla Thunderbird 0.8 (X11/20040913) X-Accept-Language: en, en-us MIME-Version: 1.0 To: Scott Marlowe Cc: PgSQL ADMIN Subject: Re: Rules on a view overwrite default values. Any way to References: <41D97A9C.6050401@kieser.net> <1104772155.23251.10.camel@state.g2switchworks.com> In-Reply-To: <1104772155.23251.10.camel@state.g2switchworks.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.331 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/10 X-Sequence-Number: 15869 Hey Scott, that's ingenious, only thing is that I use NOT NULL for many data columns too, where the value may or may not be passed in. Will try with a COALESCE and will post back here. Thx again! Brad Scott Marlowe wrote: >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. > > >