Re: Rules on a view overwrite default values. Any way to

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