Test für die null in der Funktion mit unterschiedlichen Parametern

Ich bin nicht einverstanden mit einigen der Beratung in anderen Antworten. Diese kann getan werden, mit plpgsql und ich denke, es ist meist weit überlegen Montage-Abfragen in einer client-Anwendung. Es ist schneller und sauberer, und die app sendet nur das absolute minimum über dem Draht auf Anfragen. SQL-Anweisungen gespeichert sind, die innerhalb der Datenbank, der es einfacher macht zu halten - es sei denn, Sie möchten sammeln Sie alle business-Logik im client-Anwendung, dies hängt von der Allgemeinen Architektur.

Allgemeine Hinweise

• Brauchen Sie nicht, Sie Klammern, um die SELECT mit RETURN QUERY

• Nie Verwendung name und id als Spaltennamen. Sie sind nicht aussagekräftig und wenn Sie an einen Haufen von Tabellen (wie Sie a lot in einer relationalen Datenbank), die Sie am Ende mit mehreren Spalten, die alle den Namen name oder id. Duh.

• Bitte formatieren Sie Ihre SQL-richtig-zumindest bei Fragen, Fragen der öffentlichkeit. Aber tun Sie es auch privat, für Ihre eigenen gut.

PL/pgSQL-Funktion

CREATE OR REPLACE FUNCTION func(
      _ad_nr       int  = NULL
    , _ad_nr_extra text = NULL
    , _ad_info     text = NULL
    , _ad_postcode text = NULL
    , _sname       text = NULL
    , _pname       text = NULL
    , _cname       text = NULL)
  RETURNS TABLE(id int, match text, score int, nr int, nr_extra text
              , info text, postcode text, street text, place text
              , country text, the_geom geometry) AS
$func$
BEGIN

-- RAISE NOTICE '%', -- for debugging
RETURN QUERY EXECUTE concat(
$$SELECT a.id, 'address'::text, 1 AS score, a.ad_nr, a.ad_nr_extra
     , a.ad_info, a.ad_postcode$$

, CASE WHEN (_sname, _pname, _cname) IS NULL THEN ', NULL::text' ELSE ', s.name' END  -- street
, CASE WHEN (_pname, _cname) IS NULL         THEN ', NULL::text' ELSE ', p.name' END  -- place
, CASE WHEN _cname IS NULL                   THEN ', NULL::text' ELSE ', c.name' END  -- country
, ', a.wkb_geometry'

, concat_ws('
JOIN   '
, '
FROM   "Addresses" a'
, CASE WHEN NOT (_sname, _pname, _cname) IS NULL THEN '"Streets"   s ON s.id = a.street_id' END
, CASE WHEN NOT (_pname, _cname) IS NULL         THEN '"Places"    p ON p.id = s.place_id' END
, CASE WHEN _cname IS NOT NULL                   THEN '"Countries" c ON c.id = p.country_id' END
)

, concat_ws('
AND    '
   , '
WHERE  TRUE'
   , CASE WHEN $1 IS NOT NULL THEN 'a.ad_nr = $1' END
   , CASE WHEN $2 IS NOT NULL THEN 'a.ad_nr_extra = $2' END
   , CASE WHEN $3 IS NOT NULL THEN 'a.ad_info = $3' END
   , CASE WHEN $4 IS NOT NULL THEN 'a.ad_postcode = $4' END
   , CASE WHEN $5 IS NOT NULL THEN 's.name = $5' END
   , CASE WHEN $6 IS NOT NULL THEN 'p.name = $6' END
   , CASE WHEN $7 IS NOT NULL THEN 'c.name = $7' END
)
)
USING $1, $2, $3, $4, $5, $6, $7;

END
$func$ LANGUAGE plpgsql;

Nennen:

SELECT * FROM func(1, '_ad_nr_extra', '_ad_info', '_ad_postcode', '_sname');

SELECT * FROM func(1, _pname := 'foo');

• Da ich default-Werte für alle Parameter der Funktion, die Sie verwenden können, positions - notation, namens notation oder gemischt Schreibweise im Aufruf der Funktion. Mehr in diesem Zusammenhang zu beantworten:
  
  Funktionen mit einer Variablen Anzahl von input-Parametern

• Weitere Erklärung auf die Grundlagen der dynamischen SQL finden Sie in diesem Zusammenhang beantworten:
  
  Umgestalten einer PL/pgSQL-Funktion zum zurückgeben der Ausgabe an verschiedene SELECT-Abfragen

• Den concat() Funktion ist maßgeblich für den Aufbau der saite. Es war eingeführt mit Postgres 9.1.

• Den ELSE Zweig der CASE - Anweisung standardmäßig auf NULL wenn nicht vorhanden. Vereinfacht den code.

• Den Klausel für EXECUTE macht SQL-injection unmöglich und erlaubt es, parameter, Werte direkt, genau wie prepared statements.

• NULL Werte werden verwendet, um Parameter ignorieren hier. Sie sind eigentlich nicht verwendet, zu suchen.

Einfache SQL-Funktion

Du könnte tun Sie es mit einem einfachen SQL-Funktion, und vermeiden Sie dynamische SQL. Bei einigen Fällen kann dies schneller sein. Aber ich würde nicht erwarten, dass es in diesem Fall. Re-Planung die Abfrage mit oder ohne joins und where-Bedingungen ist der überlegene Ansatz. Es wird Ihnen optimierte Abfragepläne. Planung der Kosten für eine einfache Abfrage, wie ist dies praktisch zu vernachlässigen.

CREATE OR REPLACE FUNCTION func_sql(
     _ad_nr       int  = NULL
   , _ad_nr_extra text = NULL
   , _ad_info     text = NULL
   , _ad_postcode text = NULL
   , _sname       text = NULL
   , _pname       text = NULL
   , _cname       text = NULL)
  RETURNS TABLE(id int, match text, score int, nr int, nr_extra text
              , info text, postcode text, street text, place text
              , country text, the_geom geometry) AS 
$func$

SELECT a.id, 'address' AS match, 1 AS score, a.ad_nr, a.ad_nr_extra
     , a.ad_info, a.ad_postcode
     , s.name AS street, p.name AS place
     , c.name AS country, a.wkb_geometry
FROM   "Addresses"      a
LEFT   JOIN "Streets"   s ON s.id = a.street_id
LEFT   JOIN "Places"    p ON p.id = s.place_id
LEFT   JOIN "Countries" c ON c.id = p.country_id
WHERE ($1 IS NULL OR a.ad_nr = $1)
AND   ($2 IS NULL OR a.ad_nr_extra = $2)
AND   ($3 IS NULL OR a.ad_info = $3)
AND   ($4 IS NULL OR a.ad_postcode = $4)
AND   ($5 IS NULL OR s.name = $5)
AND   ($6 IS NULL OR p.name = $6)
AND   ($7 IS NULL OR c.name = $7)

$func$ LANGUAGE sql;

Identischen Anruf.

Effektiv ignorieren Parameter mit NULL Werte:

($1 IS NULL OR a.ad_nr = $1)

Wenn Sie wirklich wollen, zu verwenden NULL-Werte als Parameter, verwenden Sie dieses Konstrukt statt:

($1 IS NULL AND a.ad_nr IS NULL OR a.ad_nr = $1)  -- AND binds before OR

Dies ermöglicht auch Indizes verwendet werden.

Auch ersetzen Sie alle Instanzen von LEFT JOIN mit JOIN.

SQL Fiddle mit vereinfachten demo für alle Varianten.

InformationsquelleAutor Erwin Brandstetter

Wenn Sie können die Abfrage ändern, Sie konnte etwas tun, wie

and (ad_postcode = $4 OR $4 IS NULL)

InformationsquelleAutor bma