Re: A question about rules

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.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com