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

Hmm... I have tested this and it works a treat for cols that I ONLY want
to put the default values into, but sadly the coalesce function doesn't
accept default as one of its parameters, so I can't use this for columns
that I want to default only if null.

:-(


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.
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>