A question about rules

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