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 13BF53A3CF8 for ; Mon, 3 Jan 2005 17:32:56 +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 50630-07 for ; Mon, 3 Jan 2005 17:32:43 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 6F99E3A3C06 for ; Mon, 3 Jan 2005 17:31:00 +0000 (GMT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 3C7AC3532C; Mon, 3 Jan 2005 09:30:59 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3AEF235284; Mon, 3 Jan 2005 09:30:59 -0800 (PST) Date: Mon, 3 Jan 2005 09:30:59 -0800 (PST) From: Stephan Szabo To: Bradley Kieser Cc: PgSQL ADMIN Subject: Re: Rules on a view overwrite default values. Any way to In-Reply-To: <41D97A9C.6050401@kieser.net> Message-ID: <20050103092722.M55197@megazone.bigpanda.com> References: <41D97A9C.6050401@kieser.net> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/12 X-Sequence-Number: 15871 On Mon, 3 Jan 2005, 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. I believe the easiest way is to attach a default to the view column using ALTER TABLE viewname ALTER COLUMN viewcolumn SET DEFAULT defaultexpr.