On Sun, Jan 19, 2020 at 08:52:36AM -0800, Adrian Klaver wrote: > On 1/19/20 2:32 AM, stan wrote: > Please post to list also. > Ccing list. > > > On Sat, Jan 18, 2020 at 08:56:06AM -0800, Adrian Klaver wrote: > > > On 1/18/20 8:53 AM, stan wrote: > > > > So, I just discovered the rules system. As I understand it, it can be used > > > > to rewrite queries before they are passed to the query processor. > > > > > > > > I was wondering if I could use this to resolve a long standing frustration > > > > of mine. I often need to declare a column as NON NULL, and create a > > > > trigger/function to auto populate it with something more complex than a > > > > simple sequence. This need arises fairly often for me, and I would like to > > > > come up with a good way to implement this. > > > > > > Stay away from rules, they will only drive you to distraction. Stick with > > > triggers they are a lot easier to understand and implement. > > > > > > > I use triggers quite a bit. My issue is that if I have a column defined as > > NOT NULL, and assign a trigger that fires a function to auto populate this > > column if the user (input form) does not. The NOT NULL constraint is > > checked BEFORE the trigger can fire, which means that I cannot properly > > provide data integrity. > > > > I don't know that this changes with rules. In other words I believe > constraints are checked first with either rules/triggers. Someone else will > need to confirm this. > > A work around is to supply a dummy DEFAULT for the the NOT NULL column e.g. > for a varchar column the string 'NOT NULL'. Test for this in the trigger and > take the appropriate action. Or accept the reality which is, you are > allowing NULL input to the column and take away the NOT NULL constraint and > look for NULL or empty string input and take the appropriate action. > Thanks, the dea of a default is an interesting one. I will consider that solution. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
