Обсуждение: 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