Thread: Rule system (and triggers)

Rule system (and triggers)

From
Thiemo Kellner
Date:
Hi all

I am afraid, I have not understood the rule system yet.

I have got 4 tables and a view over all of them.

ladevorgaenge  kanton  tarifgruppe
0..1   \       |      /
            \      |     /
             \     |    /
              |    |    |
             /|\ /|\ /|\
           tarif_progressiv

An DML onto the view tarif_progressiv_denorm is not possible because of 
the joins. So I intended to create rules to handle that. However, the do 
not behave as I expect.

INSERT INTO
     "budget"."tarif_progressiv_denorm"
     (
         "kantonscode",
         "kantonsname",
         "tarifgruppencode",
         "kinder_anzahl",
         "kirchensteuer_flag",
         "einkommen_steuerbares_range_low_boundary",
         "einkommen_steuerbares_range_high_boundary",
         "tarifschritt",
         "mindeststeuer",
         "steuersatz",
         "ladevorgaenge⠒id"
     )
     VALUES
     (
         'AG',
         null,
         'M',
         1,
         false,
         10,
         30,
         10,
         10,
         20,
         '0562b97a-87af-4071-b56d-f25b4e9bca0f'
     );

a) Unexpected not-null constraint violationfor kanton⠒id

update tarif_progressiv_denorm
    set kantonscode = 'BE'
  where kantonscode = 'AG';

update tarif_progressiv_denorm
    set kantonsname = 'Bern'
  where kantonsname = 'Aargau';

update tarif_progressiv_denorm
    set kantonsname = 'Zürich'
  where kantonscode = 'AG';

b) I thought the following would throw a not-null constraint 
violationbecause kanton⠒id must not be null.

update tarif_progressiv_denorm
    set kantonscode =  null
  where kantonscode = 'AG';

c) I noticed that, even though the rules define logic for the other 
attributes, those do not get changed if not present in an update. While 
this is actually good, but surprises me nonetheless.

Did I miss some reading in the doc? Must I use triggers instead?

Btw, I am using DbVis against PostgreSQL 16 for the queries.

Kind regards

Thiemo

Attachment

Re: Rule system (and triggers)

From
Adrian Klaver
Date:
On 11/9/23 09:18, Thiemo Kellner wrote:
> Hi all
> 
> I am afraid, I have not understood the rule system yet.

With a few exceptions nobody does. Use triggers instead.


-- 
Adrian Klaver
adrian.klaver@aklaver.com