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

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.
>
>  
>



view thread (7+ messages)  latest in thread

Message-ID: <41D97E0D.1030202@kieser.net>
Permalink:  ../41D97E0D.1030202@kieser.net/
Also on:    postgresql.org/message-id/41D97E0D.1030202@kieser.net

 · 

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: brad@kieser.net, smarlowe@g2switchworks.com
  Subject: Re: Rules on a view overwrite default values. Any way to
  In-Reply-To: <41D97E0D.1030202@kieser.net>
  References: <41D97A9C.6050401@kieser.net> <1104772155.23251.10.camel@state.g2switchworks.com> <41D97E0D.1030202@kieser.net>

* 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