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;
