pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Bradley Kieser <brad@kieser.net>
Cc: PgSQL ADMIN <pgsql-admin@postgresql.org>
Subject: Re: Rules on a view overwrite default values. Any way to
Date: Mon, 03 Jan 2005 11:09:15 -0600
Message-ID: <1104772155.23251.10.camel@state.g2switchworks.com> (raw)
In-Reply-To: <41D97A9C.6050401@kieser.net>
References: <41D97A9C.6050401@kieser.net>

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.



view thread (7+ messages)  latest in thread

Message-ID: <1104772155.23251.10.camel@state.g2switchworks.com>
Permalink:  ../1104772155.23251.10.camel@state.g2switchworks.com/
Also on:    postgresql.org/message-id/1104772155.23251.10.camel@state.g2switchworks.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: smarlowe@g2switchworks.com, brad@kieser.net
  Subject: Re: Rules on a view overwrite default values. Any way to
  In-Reply-To: <1104772155.23251.10.camel@state.g2switchworks.com>
  References: <41D97A9C.6050401@kieser.net> <1104772155.23251.10.camel@state.g2switchworks.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox