"ORDER BY ... USING" -Klausel in PostgreSQL

Ein sehr einfaches Beispiel wäre:

> SELECT * FROM tab ORDER BY col USING <

Aber das ist langweilig, denn das ist nichts, was man mit dem traditionellen ORDER BY col ASC nicht bekommen kann.

Auch der Standardkatalog erwähnt nichts Spannendes an merkwürdigen Vergleichsfunktionen/Operatoren. Sie können eine Liste von ihnen erhalten:

    > SELECT amoplefttype::regtype, amoprighttype::regtype, amopopr::regoper 
      FROM pg_am JOIN pg_amop ON pg_am.oid = pg_amop.amopmethod 
      WHERE amname = 'btree' AND amopstrategy IN (1,5);

Sie werden bemerken, dass es meistens < und > Funktionen für primitive Typen wie integer, date usw. und einige mehr für Arrays und Vektoren und so weiter gibt. Keiner dieser Operatoren hilft Ihnen dabei, eine benutzerdefinierte Bestellung zu erhalten.

In den meisten Fällen , in denen eine benutzerdefinierte Bestellung erforderlich ist, können Sie beispielsweise ... ORDER BY somefunc(tablecolumn) ... verwenden, wobei somefunc die Werte entsprechend abbildet. Da dies mit jeder Datenbank funktioniert, ist dies auch der gebräuchlichste Weg. Für einfache Dinge können Sie sogar einen Ausdruck anstelle einer benutzerdefinierten Funktion schreiben.

Gänge hochschalten

ORDER BY ... USING ist in mehreren Fällen sinnvoll:

• Die Reihenfolge ist so ungewöhnlich, dass der Trick somefunc nicht funktioniert.
• Sie arbeiten mit einem nicht-primitiven Typ (wie point, circle oder imaginären Zahlen) und möchten sich in Ihren Abfragen nicht mit seltsamen Berechnungen wiederholen.
• Der Datensatz, den Sie sortieren möchten, ist so groß, dass die Unterstützung durch einen Index gewünscht oder sogar erforderlich ist.

Ich werde mich auf die komplexen Datentypen konzentrieren: Oft gibt es mehr als eine Möglichkeit, sie auf vernünftige Weise zu sortieren. Ein gutes Beispiel ist point: Sie können sie nach dem Abstand zu (0,0) "ordnen" oder nach x zuerst, dann nach y oder einfach durch y oder irgendetwas anderes, was Sie wollen.

Natürlich hat PostgreSQL vordefinierte Operatoren für point:

    > CREATE TABLE p ( p point );
    > SELECT p <-> point(0,0) FROM p;

Aber keine von ihnen ist standardmäßig für ORDER BY verwendbar (siehe oben):

    > SELECT * FROM p ORDER BY p;
    ERROR:  could not identify an ordering operator for type point
    TIP:  Use an explicit ordering operator or modify the query.

Einfache Operatoren für point sind die Operatoren <^ und >^ "unter" und "über". Sie vergleichen einfach den y Teil des Punktes. Aber:

    >  SELECT * FROM p ORDER BY p USING >^;
    ERROR: operator > is not a valid ordering operator
    TIP: Ordering operators must be "<" or ">" members of __btree__ operator families.

ORDER BY USING erfordert einen Operator mit definierter Semantik: Offensichtlich muss es sich um einen binären Operator handeln, er muss den gleichen Typ wie Argumente akzeptieren und einen Booleschen Wert zurückgeben. Ich denke, es muss auch transitiv sein (wenn a <b und b <c dann a <c). Möglicherweise gibt es weitere Anforderungen. Alle diese Anforderungen sind jedoch auch für eine ordnungsgemäße Btree - Indexreihenfolge erforderlich. Dies erklärt die seltsamen Fehlermeldungen, die den Verweis auf btree enthalten.

ORDER BY USING erfordert auch, dass nicht nur ein Operator definiert wird, sondern eine Operator-Klasse und eine Betreiberfamilie . Während ein mit nur einem Operator sortieren konnte , versucht PostgreSQL, effizient zu sortieren und Vergleiche zu minimieren. Daher werden mehrere Operatoren verwendet, auch wenn Sie nur einen angeben - die anderen müssen bestimmte mathematische Einschränkungen einhalten -, die Transitivität habe ich bereits erwähnt, aber es gibt noch weitere.

Gang hochschalten

Definieren wir etwas Passendes: Einen Operator für Punkte, der nur den Teil y vergleicht.

Der erste Schritt besteht darin, eine benutzerdefinierte Operatorfamilie zu erstellen, die von der Indexzugriffsmethode btree verwendet werden kann. siehe

    > CREATE OPERATOR FAMILY xyzfam USING btree;   -- superuser access required!
    CREATE OPERATOR FAMILY

Als nächstes müssen wir eine Komparatorfunktion bereitstellen, die -1, 0, +1 zurückgibt, wenn zwei Punkte verglichen werden. Diese Funktion WIRD intern aufgerufen!

    > CREATE FUNCTION xyz_v_cmp(p1 point, p2 point) RETURNS int 
      AS $$BEGIN RETURN btfloat8cmp(p1[1],p2[1]); END $$ LANGUAGE plpgsql;
    CREATE FUNCTION

Als nächstes definieren wir die Operator-Klasse für die Familie. Siehe Handbuch zur Erläuterung der Nummern.

    > CREATE OPERATOR CLASS xyz_ops FOR TYPE point USING btree FAMILY xyzfam AS 
        OPERATOR 1 <^ ,
        OPERATOR 3 ?- ,
        OPERATOR 5 >^ ,
        FUNCTION 1 xyz_v_cmp(point, point) ;
    CREATE OPERATOR CLASS

Dieser Schritt kombiniert mehrere Operatoren und Funktionen und definiert auch deren Beziehung und Bedeutung. Zum Beispiel bedeutet OPERATOR 1: Dies ist der Operator für less-than Tests.

Jetzt können die Operatoren <^ und >^ in ORDER BY USING verwendet werden:

> INSERT INTO p SELECT point(floor(random()*100), floor(random()*100)) FROM generate_series(1, 5);
INSERT 0 5
> SELECT * FROM p ORDER BY p USING >^;
    p    
---------
 (17,8)
 (74,57)
 (59,65)
 (0,87)
 (58,91)

Voila - sortiert nach y .

Um es zusammenzufassen: ORDER BY ... USING ist ein interessanter Blick unter die Haube von PostgreSQL. Aber nichts, was Sie in absehbarer Zeit benötigen, es sei denn, Sie arbeiten in sehr spezifischen Bereichen der Datenbanktechnologie.

Ein weiteres Beispiel ist in den Postgres-Dokumenten mit dem Quellcode für das Beispiel hier und hier . Dieses Beispiel zeigt auch, wie die Operatoren erstellt werden.