In Function where-Bedingung mit möglichen Nullwerten formulieren

Hi Forum,

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 ?

Bin mir nicht ganz sicher, Dich richtig zu verstehen, aber ich versuchs mal:

test=*# select * from t_x;
 id | a | b
----+---+---
  1 |   | 1
  2 |   | 1
  3 |   | 1
  4 | 2 | 2
  5 | 2 | 2
  6 | 2 | 1
(6 rows)

test=*# select count(1) from t_x where a=b and a is not null;
 count
-------
     2
(1 row)

Andreas

Okay, jetzt weiß ich erst mal, was du willst, sorry.

Es geht im Prinzip um meine Schreibfaulheit :-).

Ich befürchte, dass ich um die Case Anweisungen nicht rummkomme.

Das Problem sind ja die Vergleiche mit NULL.

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.


Die Treffer zu zählen ist dann trivial …


Andreas

Ja, das Problem sind die eventuell auftretenden Null-Werte. (sorry, sollte vielleicht besser posten).

Das zwingt einen in der where-Bedingung Null-Werte abzufangen, weil der Vergleich dann nicht mit ‘=’ sondern mit ‘is null’ formuliert werden muss.

Ich such’ tatsächlich eine allgemeine (kurze) Lösung. coalesce() hilft dann leider nicht (alle Werte zulässig).


Edit: Typo

Okay:

test=*# select * from t_x where (coalesce(a::text,'NULL'),coalesce(b::text,'NULL')) = (2::text,2::text);
 id | a | b
----+---+---
  4 | 2 | 2
  5 | 2 | 2
(2 Zeilen)

Zeit: 0,507 ms
test=*# select * from t_x where (coalesce(a::text,'NULL'),coalesce(b::text,'NULL')) = ('NULL',1::text);
 id | a | b
----+---+---
  1 |   | 1
  2 |   | 1
  3 |   | 1
(3 Zeilen)

.oO( PostgreSQL ist, wenn es dennoch geht )

Andreas

Schlimm, schlimm, schlimm :slight_smile:

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 ?

Ich vergleich’s noch mal genauer.

Irgendwann gibt’s irgendwo irgendeine Anzahl Bier !

Ja, CASE kostet schon etwas.

Btw.: auch ich war heute überrascht.

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.

Andreas

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 :-).

Ganz exakt diesen.

Andreas

Ok, dann mal ein schönes Wochenende.