pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Rule system (and triggers)
2+ messages / 2 participants
[nested] [flat]

* Rule system (and triggers)
@ 2023-11-09 17:18  Thiemo Kellner <thiemo@gelassene-pferde.biz>
  0 siblings, 1 reply; 2+ messages in thread

From: Thiemo Kellner @ 2023-11-09 17:18 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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

Attachments:

tarif_progressiv.pg_sql (text/plain, 2.7K) download drop table if exists BUDGET.TARIF_PROGRESSIV cascade; create table BUDGET.TARIF_PROGRESSIV (ID uuid default gen_random_uuid() constraint PK⠒TG⠒ID primary key, KANTON⠒ID uuid not null constraint FK⠒TP⠒K⠒ID references BUDGET_MASTER.KANTON (ID), TARIFGRUPPE⠒ID uuid not null constraint FK⠒TP⠒TG⠒ID references BUDGET_MASTER.TARIFGRUPPE (ID), KINDER_ANZAHL smallint not null, KIRCHENSTEUER_FLAG boolean not null, EINKOMMEN_STEUERBARES_RANGE numrange not null, TARIFSCHRITT numeric(1000, 2) not null, MINDESTSTEUER numeric(1000, 2) not null, STEUERSATZ numeric(1000, 998) not null, LADEVORGAENGE⠒ID uuid constraint FK⠒TP⠒LV⠒ID references BUDGET_LOG.LADEVORGAENGE (ID), constraint UK⠒TP⠒UQ unique (KANTON⠒ID, TARIFGRUPPE⠒ID, KINDER_ANZAHL, KIRCHENSTEUER_FLAG, EINKOMMEN_STEUERBARES_RANGE)); comment on column BUDGET.TARIF_PROGRESSIV.ID is 'Künstlicher Schlüssel'; comment on column BUDGET.TARIF_PROGRESSIV.KINDER_ANZAHL is ''; comment on column BUDGET.TARIF_PROGRESSIV.KIRCHENSTEUER_FLAG is 'Muss Kirchensteuer eingerechnet werden'; comment on column BUDGET.TARIF_PROGRESSIV.EINKOMMEN_STEUERBARES_RANGE is ''; comment on column BUDGET.TARIF_PROGRESSIV.TARIFSCHRITT is ''; comment on column BUDGET.TARIF_PROGRESSIV.MINDESTSTEUER is ''; comment on column BUDGET.TARIF_PROGRESSIV.STEUERSATZ is ''; comment on table BUDGET.TARIF_PROGRESSIV is 'Tarife progressiver Quellensteuer $Id$'; commit; -- In contrast to Oracle, ddls do not commit implicitly.
ladevorgaenge.pg_sql (text/plain, 900B) download drop table if exists BUDGET_LOG.LADEVORGAENGE cascade; create table if not exists BUDGET_LOG.LADEVORGAENGE (ID uuid default gen_random_uuid() constraint PK⠒LV⠒ID primary key, LADEVORGANG_PT timestamp default current_timestamp not null constraint UK⠒LV⠒LADEVORGANG_PT unique); comment on table BUDGET_LOG.LADEVORGAENGE is 'Logtabelle, welche die Zeitpunkte von Ladevorgängen enthält $Id: ladevorgaenge.pg_sql 2639 2023-10-26 16:32:50Z thiemo $'; comment on column BUDGET_LOG.LADEVORGAENGE.ID is 'Primärschlüssel'; comment on column BUDGET_LOG.LADEVORGAENGE.LADEVORGANG_PT is 'Zeitpunkt, wann der Ladevorgang stattgefunden hat'; commit;
tarifgruppe.pg_sql (text/plain, 3.8K) download drop table if exists BUDGET_MASTER.TARIFGRUPPE cascade; create table if not exists BUDGET_MASTER.TARIFGRUPPE (ID uuid default gen_random_uuid() constraint PK⠒TG⠒ID primary key, CODE varchar(2) not null constraint UK⠒TG⠒CODE unique, BESCHREIBUNG text not null constraint UK⠒TG⠒BESCHREIBUNG unique, LADEVORGAENGE⠒ID uuid constraint FK⠒TG⠒LV⠒ID references BUDGET_LOG.LADEVORGAENGE (ID)); insert into BUDGET_MASTER.TARIFGRUPPE (CODE, BESCHREIBUNG) values ('A', 'Tarif für alleinstehende Personen'), ('B', 'Tarif für verheiratete Alleinverdiener'), ('C', 'Tarif für verheiratete Doppelverdiener'), ('D', 'Tarif für Personen, denen Beiträge an die AHV zurückerstattet werden'), ('E', 'Tarif für Einkünfte, die im vereinfachten Abrechnungsverfahren besteuert werden'), ('F', 'Tarif für Grenzgänger aus Italien, deren Ehegatte ausserhalb der Schweiz erwerbstätig ist'), ('G', 'Tarif für Ersatzeinkünfte, die nicht über die Arbeitgeber an die quellensteuerpflichtigen Personen ausbezahlt werden'), ('H', 'Tarif für alleinstehende Personen, die mit Kindern oder unterstützungsbedürftigen Personen im gleichen Haushalt zusammenleben und deren Unterhalt zur Hauptsache bestreiten'), ('L', 'Tarif für Grenzgänger aus Deutschland, welche die Voraussetzungen für den Tarifcode A erfüllen'), ('M', 'Tarif für Grenzgänger aus Deutschland, welche die Voraussetzungen für den Tarifcode B erfüllen'), ('N', 'Tarif für Grenzgänger aus Deutschland, welche die Voraussetzungen für den Tarifcode C erfüllen'), ('P', 'Tarif für Grenzgänger aus Deutschland, welche die Voraussetzungen für den Tarifcode H erfüllen'), ('Q', 'Tarif für Grenzgänger aus Deutschland, welche die Voraussetzungen für den Tarifcode G erfüllen'), ('R', 'Ausschließlich Tessin: Tarif Grenzgänger aus Italien, welche die Voraussetzungen für den Tarifcode A erfüllen'), ('S', 'Ausschließlich Tessin: Tarif Grenzgänger aus Italien, welche die Voraussetzungen für den Tarifcode B erfüllen'), ('T', 'Ausschließlich Tessin: Tarif Grenzgänger aus Italien, welche die Voraussetzungen für den Tarifcode C erfüllen'), ('U', 'Ausschließlich Tessin: Tarif Grenzgänger aus Italien, welche die Voraussetzungen für den Tarifcode H erfüllen'), ('HE', 'Tarif für Verwaltungsräte'), ('ME', 'Tarif für Mitarbeiterbeteiligungen'), ('NO', 'Im Falle einer Korrektur für Personen, die fälschlicherweise an der Quelle besteuert wurden bzw. die fälschlicherweise nicht an der Quelle besteuert wurden'), ('SF', 'Tarif für Grenzgänger aus Frankreich nach der Sondervereinbarung der Kantone BE, BS, BL, JU, NE, SO, VD und VS'); -- CODE, BESCHREIBUNG commit; comment on column BUDGET_MASTER.TARIFGRUPPE.ID is 'Künstlicher Schlüssel'; comment on column BUDGET_MASTER.TARIFGRUPPE.CODE is 'Gruppe'; comment on column BUDGET_MASTER.TARIFGRUPPE.BESCHREIBUNG is 'in Deutsch'; comment on table BUDGET_MASTER.TARIFGRUPPE is 'Enumeration der Tarifgruppen $Id$'; commit; -- In contrast to Oracle, ddls do not commit implicitly.
kanton.pg_sql (text/plain, 2.1K) download drop table if exists BUDGET_MASTER.KANTON cascade; create table if not exists BUDGET_MASTER.KANTON (ID uuid default gen_random_uuid() constraint PK⠒K⠒ID primary key, CODE varchar(2) not null constraint UK⠒K⠒CODE unique, NAME text not null constraint UK⠒K⠒NAME unique, LADEVORGAENGE⠒ID uuid constraint FK⠒K⠒LV⠒ID references BUDGET_LOG.LADEVORGAENGE (ID)); insert into BUDGET_MASTER.KANTON (CODE, NAME) values ('AG', 'Aargau'), ('AI', 'Appenzell Innerrhoden'), ('AR', 'Appenzell Ausserrhoden'), ('BE', 'Bern'), ('BL', 'Basel-Landschaft'), ('BS', 'Basel-Stadt'), ('FR', 'Freiburg'), ('GE', 'Genf'), ('GL', 'Glarus'), ('GR', 'Graubünden'), ('JU', 'Jura'), ('LU', 'Luzern'), ('NE', 'Neuenburg'), ('NW', 'Nidwalden'), ('OW', 'Obwalden'), ('SG', 'St. Gallen'), ('SH', 'Schaffhausen'), ('SO', 'Solothurn'), ('SZ', 'Schwyz'), ('TG', 'Thurgau'), ('TI', 'Tessin'), ('UR', 'Uri'), ('VD', 'Waadt'), ('VS', 'Wallis'), ('ZG', 'Zug'), ('ZH', 'Zürich'); -- CODE, NAME comment on column BUDGET_MASTER.KANTON.ID is 'Künstlicher Schlüssel'; comment on column BUDGET_MASTER.KANTON.CODE is 'Abkürzung des Kantonsnamen'; comment on column BUDGET_MASTER.KANTON.NAME is 'in Deutsch'; comment on table BUDGET_MASTER.KANTON is 'Enthält die Aufzöhlung aller Schweizer Kantone $Id$'; commit; -- In contrast to Oracle, ddls do not commit implicitly.
tarif_progressiv_denorm.pg_sql (text/plain, 18.1K) download drop view if exists BUDGET.TARIF_PROGRESSIV_DENORM; create or replace view BUDGET.TARIF_PROGRESSIV_DENORM as select K.CODE as KANTONSCODE, K.NAME as KANTONSNAME, TG.CODE as TARIFGRUPPENCODE, TG.BESCHREIBUNG as TARIFGRUPPENBESCHREIBUNG, TP.KINDER_ANZAHL, TP.KIRCHENSTEUER_FLAG, TP.EINKOMMEN_STEUERBARES_RANGE, lower(TP.EINKOMMEN_STEUERBARES_RANGE) as EINKOMMEN_STEUERBARES_RANGE_LOW_BOUNDARY, upper(TP.EINKOMMEN_STEUERBARES_RANGE) as EINKOMMEN_STEUERBARES_RANGE_HIGH_BOUNDARY, TP.TARIFSCHRITT, TP.MINDESTSTEUER, TP.STEUERSATZ, LV.LADEVORGANG_PT, TP.LADEVORGAENGE⠒ID, TP.ID from BUDGET.TARIF_PROGRESSIV TP inner join BUDGET_LOG.LADEVORGAENGE LV on TP.LADEVORGAENGE⠒ID = LV.ID inner join BUDGET_MASTER.KANTON K on TP.KANTON⠒ID = K.ID inner join BUDGET_MASTER.TARIFGRUPPE TG on TP.TARIFGRUPPE⠒ID = TG.ID; comment on column BUDGET.TARIF_PROGRESSIV_DENORM.KANTONSCODE is 'Abkürzung des Kantonsnamen'; comment on column BUDGET.TARIF_PROGRESSIV_DENORM.LADEVORGANG_PT is 'Zeitpunkt, wann der Ladevorgang war, der den Datensatz in die Tabelle geschrieben hat. Dies ist unbesehen von Vorgängen, z. B. Konsolidierung, die den Satz noch verändern.'; comment on view BUDGET.TARIF_PROGRESSIV_DENORM is 'Informationen progressiver Tarife angereichert mit Kantos-, Tarifgruppen- und Ladeinformationen $Id$'; -- create or replace function BUDGET.TARIF_PROGRESSIV_DENORM⠒INS_DEL() returns trigger -- as -- $CODE$ -- BEGIN -- raise warning 'Insert or deletes are not allowed on TARIF_PROGRESSIV_DENORM. For inserts use the batch programme. Deletes should be made as changes to the galus of WIEDERKEHREND.'; -- END; -- $CODE$ -- language plpgsql; -- create or replace trigger TARIF_PROGRESSIV_DENORM⠒INS_DEL -- before insert or delete -- on BUDGET.TARIF_PROGRESSIV_DENORM -- for each statement -- execute function BUDGET.TARIF_PROGRESSIV_DENORM⠒INS_DEL(); create or replace rule TARIF_PROGRESSIV_DENORM⠒INS as on insert to BUDGET.TARIF_PROGRESSIV_DENORM do instead insert into TARIF_PROGRESSIV (KANTON⠒ID, TARIFGRUPPE⠒ID, KINDER_ANZAHL, KIRCHENSTEUER_FLAG, EINKOMMEN_STEUERBARES_RANGE, TARIFSCHRITT, MINDESTSTEUER, STEUERSATZ, LADEVORGAENGE⠒ID) values (/* KANTON⠒ID */ case -- a disjoint when new.KANTONSCODE is not null and new.KANTONSNAME is null then (select ID from BUDGET_MASTER.KANTON where 1 = 1 and CODE = new.KANTONSCODE and 1 = 1) -- the other disjoint when new.KANTONSCODE is null and new.KANTONSNAME is not null then (select ID from BUDGET_MASTER.KANTON where 1 = 1 and NAME = new.KANTONSNAME and 1 = 1) -- check for conflicts when new.KANTONSCODE is not null and new.KANTONSNAME is not null then (select ID from BUDGET_MASTER.KANTON where 1 = 1 and CODE = new.KANTONSCODE and NAME = new.KANTONSNAME and 1 = 1) -- fallback else null end, /* TARIFGRUPPE⠒ID */ case -- a disjoint when new.TARIFGRUPPENCODE is not null and new.TARIFGRUPPENBESCHREIBUNG is null then (select ID from BUDGET_MASTER.TARIFGRUPPE where 1 = 1 and CODE = new.TARIFGRUPPENCODE and 1 = 1) -- the other disjoint when new.TARIFGRUPPENCODE is null and new.TARIFGRUPPENBESCHREIBUNG is not null then (select ID from BUDGET_MASTER.TARIFGRUPPE where 1 = 1 and BESCHREIBUNG = new.TARIFGRUPPENBESCHREIBUNG and 1 = 1) -- check for conflicts when new.TARIFGRUPPENCODE is not null and new.TARIFGRUPPENBESCHREIBUNG is not null then (select ID from BUDGET_MASTER.TARIFGRUPPE where 1 = 1 and CODE = new.TARIFGRUPPENCODE and BESCHREIBUNG = new.TARIFGRUPPENBESCHREIBUNG and 1 = 1) -- fallback else null end, /* KINDER_ANZAHL */ new.KINDER_ANZAHL, /* KIRCHENSTEUER_FLAG */ new.KIRCHENSTEUER_FLAG, /* EINKOMMEN_STEUERBARES_RANGE */ case -- most frequent and disjoint when new.EINKOMMEN_STEUERBARES_RANGE is null and ( new.EINKOMMEN_STEUERBARES_RANGE_LOW_BOUNDARY is not null or new.EINKOMMEN_STEUERBARES_RANGE_HIGH_BOUNDARY is not null) then numrange(new.EINKOMMEN_STEUERBARES_RANGE_LOW_BOUNDARY, new.EINKOMMEN_STEUERBARES_RANGE_HIGH_BOUNDARY) -- the other disjoint when new.EINKOMMEN_STEUERBARES_RANGE is not null and new.EINKOMMEN_STEUERBARES_RANGE_LOW_BOUNDARY is null and new.EINKOMMEN_STEUERBARES_RANGE_HIGH_BOUNDARY is null then new.EINKOMMEN_STEUERBARES_RANGE -- check for conflicts when new.EINKOMMEN_STEUERBARES_RANGE is not null and (new.EINKOMMEN_STEUERBARES_RANGE_LOW_BOUNDARY is not null or new.EINKOMMEN_STEUERBARES_RANGE_HIGH_BOUNDARY is not null) and new.EINKOMMEN_STEUERBARES_RANGE = numrange(new.EINKOMMEN_STEUERBARES_RANGE_LOW_BOUNDARY, new.EINKOMMEN_STEUERBARES_RANGE_HIGH_BOUNDARY) then new.EINKOMMEN_STEUERBARES_RANGE -- fallback else null end, /* TARIFSCHRITT */ new.TARIFSCHRITT, /* MINDESTSTEUER */ new.MINDESTSTEUER, /* STEUERSATZ */ new.STEUERSATZ, /* LADEVORGAENGE⠒ID */ case -- most frequent and disjoint when new.LADEVORGAENGE⠒ID is not null and new.LADEVORGANG_PT is null then new.LADEVORGAENGE⠒ID -- the other disjoint when new.LADEVORGAENGE⠒ID is null and new.LADEVORGANG_PT is not null then (select ID from BUDGET_LOG.LADEVORGAENGE where 1 = 1 and LADEVORGANG_PT = new.LADEVORGANG_PT and 1 = 1) -- check for conflicts when new.LADEVORGAENGE⠒ID is not null and new.LADEVORGANG_PT is not null then (select ID from BUDGET_LOG.LADEVORGAENGE where 1 = 1 and ID = new.LADEVORGAENGE⠒ID and LADEVORGANG_PT = new.LADEVORGANG_PT and 1 = 1) -- fallback else null end); create or replace rule TARIF_PROGRESSIV_DENORM⠒UPD as on update to BUDGET.TARIF_PROGRESSIV_DENORM do instead update TARIF_PROGRESSIV set KANTON⠒ID = case -- a disjoint when new.KANTONSCODE is not null and new.KANTONSNAME is null then (select ID from BUDGET_MASTER.KANTON where 1 = 1 and CODE = new.KANTONSCODE and 1 = 1) -- the other disjoint when new.KANTONSCODE is null and new.KANTONSNAME is not null then (select ID from BUDGET_MASTER.KANTON where 1 = 1 and NAME = new.KANTONSNAME and 1 = 1) -- check for conflicts when new.KANTONSCODE is not null and new.KANTONSNAME is not null then (select ID from BUDGET_MASTER.KANTON where 1 = 1 and CODE = new.KANTONSCODE and NAME = new.KANTONSNAME and 1 = 1) -- fallback else null end, TARIFGRUPPE⠒ID = case -- a disjoint when new.TARIFGRUPPENCODE is not null and new.TARIFGRUPPENBESCHREIBUNG is null then (select ID from BUDGET_MASTER.TARIFGRUPPE where 1 = 1 and CODE = new.TARIFGRUPPENCODE and 1 = 1) -- the other disjoint when new.TARIFGRUPPENCODE is null and new.TARIFGRUPPENBESCHREIBUNG is not null then (select ID from BUDGET_MASTER.TARIFGRUPPE where 1 = 1 and BESCHREIBUNG = new.TARIFGRUPPENBESCHREIBUNG and 1 = 1) -- check for conflicts when new.TARIFGRUPPENCODE is not null and new.TARIFGRUPPENBESCHREIBUNG is not null then (select ID from BUDGET_MASTER.TARIFGRUPPE where 1 = 1 and CODE = new.TARIFGRUPPENCODE and BESCHREIBUNG = new.TARIFGRUPPENBESCHREIBUNG and 1 = 1) -- fallback else null end, KINDER_ANZAHL = coalesce(new.KINDER_ANZAHL, old.KINDER_ANZAHL), KIRCHENSTEUER_FLAG = coalesce(new.KIRCHENSTEUER_FLAG, old.KIRCHENSTEUER_FLAG), EINKOMMEN_STEUERBARES_RANGE = coalesce(new.EINKOMMEN_STEUERBARES_RANGE, old.EINKOMMEN_STEUERBARES_RANGE), TARIFSCHRITT = coalesce(new.TARIFSCHRITT, old.TARIFSCHRITT), MINDESTSTEUER = coalesce(new.MINDESTSTEUER, old.MINDESTSTEUER), STEUERSATZ = coalesce(new.STEUERSATZ, old.STEUERSATZ) where id = old.ID; create or replace rule TARIF_PROGRESSIV_DENORM⠒DEL as on delete to BUDGET.TARIF_PROGRESSIV_DENORM do instead delete from TARIF_PROGRESSIV where id = old.id; commit;
^ permalink raw reply [nested|flat] 2+ messages in thread

* Re: Rule system (and triggers)
@ 2023-11-09 17:50  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Thiemo Kellner <thiemo@gelassene-pferde.biz>
  0 siblings, 0 replies; 2+ messages in thread

From: Adrian Klaver @ 2023-11-09 17:50 UTC (permalink / raw)
  To: Thiemo Kellner <thiemo@gelassene-pferde.biz>; pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2023-11-09 17:50 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-09 17:18 Rule system (and triggers) Thiemo Kellner <thiemo@gelassene-pferde.biz>
2023-11-09 17:50 ` Adrian Klaver <adrian.klaver@aklaver.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox