postgresql - uso - Prueba de nulo en función con parámetros variables
uso de variables en postgresql (4)
Tengo una función de Postgres:
create function myfunction(integer, text, text, text, text, text, text) RETURNS
table(id int, match text, score int, nr int, nr_extra character varying, info character varying, postcode character varying,
street character varying, place character varying, country character varying, the_geom geometry)
AS $$
BEGIN
return query (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 place , c.name country, a.wkb_geometry as wkb_geometry from "Addresses" a
left join "Streets" s on a.street_id = s.id
left join "Places" p on s.place_id = p.id
left join "Countries" c on p.country_id = c.id
where c.name = $7
and p.name = $6
and s.name = $5
and a.ad_nr = $1
and a.ad_nr_extra = $2
and a.ad_info = $3
and ad_postcode = $4);
END;
$$
LANGUAGE plpgsql;
Esta función no proporciona el resultado correcto cuando una o más de las variables ingresadas son ad_postcode = NULL porque ad_postcode = NULL fallará.
¿Qué puedo hacer para probar NULL dentro de la consulta?
No estoy de acuerdo con algunos de los consejos en otras respuestas. Esto se puede hacer con plpgsql y creo que es muy superior al ensamblaje de consultas en una aplicación cliente. Es más rápido y más limpio, y la aplicación solo envía el mínimo requerido en las solicitudes. Las sentencias SQL se guardan dentro de la base de datos, lo que hace que sea más fácil de mantener, a menos que desee recopilar toda la lógica comercial en la aplicación cliente, esto depende de la arquitectura general.
Consejo general
No necesita paréntesis alrededor de
SELECTconRETURN QUERYNunca use
nameeidcomo nombres de columna. No son descriptivos y cuando te unes a un grupo de tablas (como lo tienesa loten una base de datos relacional), terminas con varias columnas connameoid. Duh.Formatee su SQL correctamente, al menos cuando haga preguntas públicas. Pero hazlo en privado también, por tu propio bien.
Función 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;
Llamada:
SELECT * FROM func(1, ''_ad_nr_extra'', ''_ad_info'', ''_ad_postcode'', ''_sname'');
SELECT * FROM func(1, _pname := ''foo'');
Como proporcioné valores predeterminados para todos los parámetros de función, puede usar la notación posicional , la notación con nombre o la notación mixta en la llamada a la función. Más en esta respuesta relacionada:
Funciones con número variable de parámetros de entradaPara obtener más información sobre los conceptos básicos de SQL dinámico, consulte esta respuesta relacionada:
Refactorizar una función PL / pgSQL para devolver el resultado de varias consultas SELECTLa función
concat()es instrumental para construir la cadena. Fue presentado con Postgres 9.1.La rama
ELSEde una instrucciónCASEdefecto enNULLcuando no está presente. Simplifica el código.La cláusula
USINGparaEXECUTEhace que la inyección SQL sea imposible y permite usar valores de parámetros directamente, exactamente como declaraciones preparadas.NULLvaloresNULLse usan para ignorar los parámetros aquí. En realidad, no se utilizan para buscar.
Función SQL simple
Podría hacerlo con una función SQL simple y evitar el SQL dinámico. En algunos casos, esto puede ser más rápido. Pero no lo esperaría en este caso . Replaneando la consulta con o sin combinaciones y donde las condiciones son el enfoque superior. Le dará planes de consulta optimizados. El costo de planificación para una consulta simple como esta es casi insignificante.
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;
Llamada idéntica
Para ignorar efectivamente los parámetros con valores NULL :
($1 IS NULL OR a.ad_nr = $1)
Si realmente quiere usar valores NULL como parámetros , use este constructo en su lugar:
($1 IS NULL AND a.ad_nr IS NULL OR a.ad_nr = $1) -- AND binds before OR
Esto también permite que se usen índices .
También reemplace todas las instancias de LEFT JOIN con JOIN .
SQL Fiddle con demostración simplificada para todas las variantes.
Puedes usar
c.name IS NOT DISTINCT FROM $7
c.name true si c.name y $7 son iguales o ambos son null .
O puedes usar
(c.name = $7 or $7 is null )
c.name true si c.name y $7 son iguales o $7 es nulo.
Si puede modificar la consulta, podría hacer algo como
and (ad_postcode = $4 OR $4 IS NULL)
Varias cosas...
Primero, como nota al margen: la semántica de su consulta podría necesitar una revisita. Algunas de las cosas en sus cláusulas where podrían pertenecer en sus cláusulas join , como:
from ...
left join ... on ... and ...
left join ... on ... and ...
Cuando no lo hacen, lo más probable es que esté utilizando una inner join , en lugar de una left join .
En segundo lugar, hay un is not distinct from operador, que ocasionalmente puede ser útil en lugar de = . a is not distinct from b es básicamente equivalente a a = b or a is null and b is null .
Tenga en cuenta, sin embargo, que is not distinct from NO utiliza un índice, mientras que = y is null realidad. Podría usar (field = $i or $i is null) en su caso particular, y obtendrá el plan óptimo si está utilizando la última versión de Postgres: