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
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.
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;
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.
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.
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;
