Checking a null function with various parameters

Question

I have a Postgres function:

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; 

This function does not give the correct result when one or more of the entered variables is NULL, because ad_postcode = NULL does not work.

What can I do to check for NULL inside a request?

Answer 1

I disagree with some tips in other answers. This can be done using plpgsql, and I think in most cases it is much better to collect queries in the client application. It is faster and cleaner, and the application only sends the minimum minimum over the wires in the requests. SQL statements are stored inside the database, which simplifies its support - if you do not want to collect all the business logic in the client application, it depends on the general architecture.

General recommendations

• You don't need parentheses around SELECT with RETURN QUERY

• Never use name and id as column names. They are not descriptive, and when you join a bunch of tables (for example, you must a lot in a relational database), you get several columns with all the names name or id . Duh.

• Please format your SQL correctly, at least when asking public questions. But do it privately, for your own good.

PL / pgSQL Function

 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; 

Call:

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

• Since I provided default values ​​for all parameters of the function, you can use positional notation, with a name, or mixed notation in a function call. More in this related answer:
  Functions with a variable number of input parameters

• For a more detailed explanation of the basics of dynamic SQL, see this answer:
  Reorganize the PL / pgSQL function to return the output of various SELECT queries

• The concat() function is a tool for building a string. It was introduced with Postgres 9.1.

• The CASE statement's ELSE CASE defaults to NULL if not. Simplifies the code.

• The USING for EXECUTE makes SQL injection impossible and allows you to directly use parameter values, just like ready-made statements.

  Values

• NULL are used to ignore parameters here. They are not actually used for search.

Simple SQL Function

You can do this with a simple SQL function and avoid dynamic SQL. In some cases, it can be faster. But I did not expect this in this case. Rescheduling a request with or without pooling and where conditions are an excellent approach. This will give you optimized query plans. The planned cost of such a simple request is almost negligible.

 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; 

Identical call.

To effectively ignore parameters with NULL values :

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

If you really want to use NULL values ​​as parameters , use this construct instead:

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

It also allows the use of indexes .
Also replace all instances of LEFT JOIN with JOIN .

SQL Fiddle with simplified demonstration for all options.

Answer 2

you can use

 c.name IS NOT DISTINCT FROM $7 

It will return true if c.name and $7 are equal, or both are null .

Or you can use

 (c.name = $7 or $7 is null ) 

It will return true if c.name and $7 are equal or $7 is null.

Answer 3

If you can change the request, you can do something like

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

Answer 4

A few things...

Firstly, as a note: the semantics of your request may require re-viewing. Some of the things in your where clauses may actually belong to your join clauses, for example:

 from ... left join ... on ... and ... left join ... on ... and ... 

If this is not the case, you should most likely use an inner join rather than a left join .

Secondly, there is an operator is not distinct from , which can sometimes be useful instead of = . a is not distinct from b is basically equivalent to a = b or a is null and b is null .

Note that is not distinct from uses a NOT index, while = and is null really do. You can use (field = $i or $i is null) instead in your specific case, and it will give an optimal plan if you use the latest version of Postgres:

https://gist.github.com/ddebernardy/5884267