ich möchte in einer Tabelle mehrfach vorkommende Zeilen mit Hilfe einer Function zählen. Die Function bekommt Werte für die where-Bedingung übergeben.
create table t_x (
id serial,
a integer,
b integer
);
insert into t_x (a,b) values (null,1), (null,1), (null,1), (2,2), (2,2), (2,1);
create or replace function find_multiples_of(x_row t_x)
returns integer as $$
DECLARE
rc integer;
BEGIN
select count(id) into rc from t_x
where
case when x_row.a is null then
a is null
else
a = x_row.a
end
and
case when x_row.b is null then
b is null
else
b = x_row.b
end
;
return rc;
END;
$$ language plpgsql;
select find_multiples_of((null,null,1)::t_x);
select find_multiples_of((null,2,2)::t_x);
Das funktioniert soweit. Das case zu schreiben wird länglich bei vielen Spalten. Geht’s irgendwie kürzer ?
Frage: ist -1 (zum Beispiel) ein praktisch vorkommender Wert? Falls nein, reduziert sich Dein Problem auf folgendes:
test=*# select * from t_x where (coalesce(a,-1),coalesce(b,-1)) = (-1,1);
id | a | b
----+---+---
1 | | 1
2 | | 1
3 | | 1
(3 rows)
test=*# select * from t_x where (coalesce(a,-1),coalesce(b,-1)) = (2,2);
id | a | b
----+---+---
4 | 2 | 2
5 | 2 | 2
(2 rows)
Das zählen hab ich mal weggelassen, um zu sehen, was passiert. Anstatt gegen NULL zu prüfen setzt Du -1 ein, zum auslesen der Spalten nutzt Du coalesce(), um NULL in -1 zu wandeln.
Nicht nur, dass es funktioniert. Hab’s in eine Function gepackt
create or replace function cfind_multiples_of(x_row t_x)
returns integer as $$
DECLARE
rc integer;
BEGIN
select count(id) into rc from t_x
where
(coalesce(a::text,'NULL'),coalesce(b::text,'NULL')) = (coalesce(x_row.a::text,'NULL'),coalesce(x_row.b::text,'NULL'))
;
return rc;
END;
$$ language plpgsql;
und mit timing on mal geschaut, wie die Performance so ist.
Der erste Eindruck ist : es ist nicht langsamer, eher leicht schneller als die Case-Anweisung. Schlimm, schlimm, schlimm.
Kostet Case so viel ?
Man stelle sich mehrere Tabellen vor, z.B. person, email, adresse. Zu jeder Person können N adressen und email existieren. Das abzubilden also auch noch Tabellen person_adresse und person_email. Um nun da eine Person mit N email und M adressen zu speichern, brauche ich etliche Inserts. Bisher. Falls der Patch von Marko es schafft, wird das in 8.5 mit einem einzelnen Insert funktionieren. Der Performance-Gewinn ist immens, Details dazu demnächst irgendwo. Ich war einfach von David’s und Marko’s Vortrag in Paris animiert, das mal zu testen.
Meinst du den Vortrag über Writable CTEs ?
Hoffentlich nicht, sonst muss ich mich da mal reinarbeiten ;-).
Das,was du mit Person/Adresse/Email ansprichst, dafür Tabellen peson_adressen, person_email zu benutzen, weigern wir uns zu machen. Das ist Design-mässig Blödsinn. Wir versuchen’s über Updatable-Views zu lösen. Und wenn wir dabei auf die Nase fallen, haben wir wenigstens was gelernt :-).