Thread: A question about rules

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.

Sorry for such a newbie question.

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

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.

> 
> Sorry for such a newbie question.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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.klaver@aklaver.com

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