Wie entfernen von Bedingungen aus der WHERE-Klausel, wenn Parameter NULL ist

Bin ich durch ein 2-Parameter eine PL/pgSQL-Funktion. Hier ist die Abfrage:

SELECT * 
FROM table 
WHERE col1 = param1 
  AND col2 = param2

Beide Parameter kann NULL sein, in welchem Fall der entsprechende Ausdruck sollte entfernt werden aus der WHERE - Klausel.

Wie kann ich das tun? Mit IF Bedingungen?

Ist die Funktion geschrieben in plpgsql (im Gegensatz zum einfachen SQL oder eine andere austauschbare Sprachen)?
seine schlichte plppgsql

InformationsquelleAutor user3339988 | 2014-04-30

11

Vielleicht ist das der trick:
```
SELECT * 
FROM table 
WHERE col1 = param1 
  AND (param2 is null or col2 = param2);
```
Dies ist nicht die Beseitigung der UND-Zustand, aber sollte das unwichtig im Falle von param2 den Wert null. So nicht klar beantworten Ihre Frage doch herum... 😉
- ich habe geändert die Frage.Kann mir jemand beantworten das auch.
- anwenden, was Sie gelernt haben, auf diese ein. WHERE (param1 is null or col1 = param1) AND (param2 is null or col2 = param2). Natürlich wäre dies alles zurück, wenn beide eine null. Vielleicht denken Sie über gültige default-Werte zu diesem Zeitpunkt?
InformationsquelleAutor frlan

Einem einfachen (param1 IS NULL OR col1 = param1) übernimmt dies, wie schon bereits beantwortet.

Zu tatsächlich entfernen beliebige oder alle NULL-Bedingungen, die Sie benötigen, dynamische SQL -. Sie können bauen Sie Ihre Anweisung in den client-bedingt oder erstellen Sie eine plpgsql (oder einer anderen prozeduralen Sprache) - Funktion zu übernehmen. Dieser Ansatz führen kann, superior-Abfrage Pläne beim Umgang mit komplexen Abfragen.

PL/pgSQL - Funktion

Kniffligen Teile sind:

Umgang mit NULL-Werten und leeren Zeichenfolgen ordnungsgemäß in die string-Verkettung
Umgang mit Variablen Daten-Typen, während die Vermeidung von möglichen SQL-injection -

CREATE OR REPLACE FUNCTION f_conditional_where(_param1 int  = NULL
                                             , _param2 text = NULL
                                             , _param3 date = NULL)
  RETURNS SETOF tbl AS
$func$
DECLARE
   _where text :=
      concat_ws(' AND '
        , CASE WHEN _param1 IS NOT NULL THEN 'col1 = $1' END
        , CASE WHEN _param2 IS NOT NULL THEN 'col2 = $2' END
        , CASE WHEN _param3 IS NOT NULL THEN 'col3 = $3' END);   
   _sql text := 'SELECT * FROM tbl';
BEGIN

IF _where <> '' THEN
   _sql := _sql || ' WHERE ' || _where;
END IF;

-- debug output
RAISE NOTICE '
 _sql:   |%|
 _where: |%|', _sql, _where;

-- execute
RETURN QUERY EXECUTE _sql
USING $1, $2, $3;

END
$func$  LANGUAGE plpgsql;

Drei Beispiele für den Aufruf:

SELECT * FROM f_conditional_where();
SELECT * FROM f_conditional_where(1, 'foo');
SELECT * FROM f_conditional_where(_param3 := '2012-01-01', _param2 := 'foo');

SQL Fiddle.

Müssen Sie haben ein grundlegendes Verständnis von plpgsql für diese. Sie finden viele Beispiele mit reichlich Erklärung in der plpgsql - tag.

InformationsquelleAutor Erwin Brandstetter

5

Die einfachste (wenn auch wahrscheinlich nicht die effizienteste) Weg ist, behandeln die null innerhalb des SQL-Statements selbst, z.B.:
```
SELECT * 
FROM table 
WHERE col1 = param1 
AND (param2 IS NULL OR col2 = param2)
```
Diese übergibt die Klausel für alle Zeilen, wenn der parameter null ist.

Oder mit diesem trick:
```
SELECT * 
FROM table 
WHERE col1 = param1 
AND col2 = COALESCE(param2, col2)
```
Wenn param2 ist NULL ist die Bedingung äquivalent zu col2 = col2, die immer true sein solange col2 nicht selbst enthalten NULL Werte.

Einen quick-test unter Verwendung von hart codierten Werten und nicht als eine Funktion der parameter gab mir den gleichen Abfrageplan für beide Ansätze - die OR und COALESCE Teile sind scheinbar optimierten Weg, bevor die Planung der Abfrage, so ist es, als ob die zweite AND waren tatsächlich bedingt entfernt.
- +1, aber beachten Sie, dass coalesce ist keine gute Idee, weil es nicht einen index verwenden, wenn einer verfügbar ist, während die erste Variante wird.
- Ich habe gerade einige Tests mit einem hard-codierten NULL oder string statt param2, und bekam die identische Abfrage-Pläne für beide Strategien. Da es konstant für alle Zeilen, die erste wird entweder TRUE OR ... oder FALSE OR ..., die optimiert werden können, entfernt, und der zweite wird COALESCE(NULL, col2) oder COALESCE('known value', col2) ersetzt werden können durch col2 und 'known value' vor der Planung der Abfrage.
- eigentlich col2 = col2 wird nicht immer wahr sein. Wenn col2 könnte die null-Werte enthalten, in diesem Zustand konnte unknown, so dass diese Methode nicht nur nicht auf dem index, aber es könnte auch zurückgeben, falsche Ergebnisse
- Guter Punkt, um für NULL-Werte col2; ich werde eine Anmerkung hinzufügen, dass.
- Die erste Abfrage können Sie besser für die Verwendung in PL/pgSQL - die planer erstellen 2 verschiedene Pläne und wählte eine auf die Ausführungszeit.
InformationsquelleAutor IMSoP

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.