Tester la valeur null en fonction des divers paramètres

Je suis en désaccord avec certains des conseils dans d'autres réponses. Cela peut être fait avec plpgsql et je pense que c'est surtout de loin supérieur le montage de requêtes dans une application client. Il est plus rapide et plus propre et de l'application envoie uniquement le strict minimum à travers le fil dans les demandes. Les instructions SQL sont sauvegardés dans la base de données, ce qui rend plus facile à maintenir - à moins que vous souhaitez collecter toute la logique métier de l'application cliente, cela dépend de l'architecture générale.

Conseils généraux

• Vous n'avez pas besoin de parenthèses autour de la SELECT avec RETURN QUERY

• Jamais utilisation name et id que les noms de colonnes. Ils ne sont pas descriptives et lorsque vous vous joignez à un groupe de tables (comme vous devez a lot dans une base de données relationnelle), vous vous retrouvez avec plusieurs colonnes de tous les noms de name ou id. Duh.

• Veuillez format SQL correctement, au moins quand vous posez des questions publiques. Mais le faire en privé, pour votre propre bien.

Fonctions PL/pgSQL

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;

Appel:

SELECT * FROM func(1, '_ad_nr_extra', '_ad_info', '_ad_postcode', '_sname');
SELECT * FROM func(1, _pname := 'foo');

• Depuis que j'ai fourni des valeurs par défaut pour tous les paramètres de la fonction, vous pouvez utiliser de position notation, nommé notation ou mixte notation dans l'appel de fonction. Plus dans cette réponse:
  
  Fonctions avec un nombre variable de paramètres d'entrée

• Pour plus d'explication sur les bases du SQL dynamique, reportez-vous à cette réponse:
  
  Refactoriser un PL/pgSQL fonction pour renvoyer la sortie de diverses requêtes SELECT

• La concat() fonction est déterminante pour la construction de la chaîne. Il a été présenté avec Postgres 9.1.

• La ELSE branche d'un CASE déclaration par défaut NULL lorsqu'il n'est pas présent. Simplifie le code.

• La à l'AIDE de clause de EXECUTE fait de l'injection SQL impossible et permet d'utiliser des valeurs de paramètre directement, exactement comme les requêtes préparées.

• NULL valeurs sont utilisées pour ignorer les paramètres ici. Ils ne sont pas utilisés pour la recherche.

Simple de la fonction SQL

Vous pourrait le faire avec une simple fonction SQL et éviter SQL dynamique. Pour certains cas, cela peut être plus rapide. Mais je n'allais pas l'attendre dans ce cas. Re-planification de la requête avec ou sans joint et où les conditions de travail est la meilleure approche. Il vous donnera optimisé les plans de requête. Coûts de la planification pour une simple requête de ce genre est presque négligeable.

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;

Identiques appel.

Efficacement ignorer les paramètres avec NULL valeurs:

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

Si vous voulez vraiment utiliser les valeurs NULL en tant que paramètres, utilisez cette construction à la place:

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

Cela permet aussi index à être utilisé.

Également remplacer toutes les instances de LEFT JOIN avec JOIN.

SQL Violon avec simplifiée de démonstration pour toutes les variantes.

Si vous pouvez modifier la requête, vous pourriez faire quelque chose comme

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