[SQL] Help with optional parameters

[Rob Tester]

I have the need to have optional values for a query in a stored procedure that I am building. (using postgres 8.1.4). This particular query executes against a huge table (several million rows) and has six optional parameters that can be sent to the function. If one of the parameters is null then the parameter doesn't need to be included in the query. Also the values in the columns that are optional can contain NULL values. One way that will work (although extremely cumbersome) is to have a switch on the parameters to execute the correct query: 

 
--This is a sample
IF (a IS NULL AND b IS NULL) THEN
    select * from my_table;
ELSEIF (a IS NOT NULL and b IS NULL) THEN
    select * from my_table where a=parama;
ELSEIF (a IS NULL and b IS NOT NULL) THEN
    select * from my_table where b=paramb;
ELSE
    select * from my_table where a=parama AND b=paramb;
ENDIF;
 
This is extremely bad when you have 6 parameters giving 64 possible queries.
 
I tried using this (which works) but the planner likes to throw out the index for the columns because of the OR condition:
 
select * from my_table WHERE (parama IS NULL OR a=parama) AND (paramb IS NULL OR b=paramb);
 
 
My next thought was to get the planner to think that using indexes would be a good thing so I did the following:
 
select * from my_table WHERE a=COALESCE(parama,a) AND b=COALESCE(paramb,b);
 
That works great unless the column value for a or b IS NULL in which case NULL<>NULL because it equals NULL.
 
Then I used the standby: set transform_null_equals to 1
 
This allows select null=null to return true.
 
However, I ran into the problem that a=a (when a is a NULL value) still equals NULL. But a=NULL is true. So it didn't work out.
 
What is the best way to write a query and get the planner to use indexes when you have optional parameters and columns that can contain NULL values?

Re: [SQL] Help with optional parameters

[Rob Tester]

This seems to be the best solution. At first I was a bit skeptical about the perfomance using execute versus using pre-planned queries. However, I found that this model actually executes faster than the paramterized queries that I was trying (even using the if-then-endif model). 

 
Thanks for this tip. 
On 8/17/06, MaXX <[EMAIL PROTECTED]> wrote:
Rob Tester wrote:> I have the need to have optional values for a query in a stored procedure> that I am building. (using postgres 
8.1.4). This particular query> executes> against a huge table (several million rows) and has six optional> parameters> that can be sent to the function. If one of the parameters is null> then the
> parameter doesn't need to be included in the query. Also the values in> the> columns that are optional can contain NULL values. One way that will work> (although extremely cumbersome) is to have a switch on the parameters to
> execute the correct query:>> --This is a sample> IF (a IS NULL AND b IS NULL) THEN>select * from my_table;> ELSEIF (a IS NOT NULL and b IS NULL) THEN>select * from my_table where a=parama;
> ELSEIF (a IS NULL and b IS NOT NULL) THEN>select * from my_table where b=paramb;> ELSE>select * from my_table where a=parama AND b=paramb;> ENDIF;>> This is extremely bad when you have 6 parameters giving 64 possible
> queries.>You can try something like this, it should show the basis,CREATE OR REPLACE FUNCTION test(int4, int4) RETURNS text AS$BODY$DECLAREa ALIAS FOR $1;b ALIAS FOR $2;
-- add as many as input paramquery_base text;has_param bool;query_where text;query_final text;BEGINquery_base := 'SELECT * FROM my_table ';has_param := FALSE;query_where := '';
IF (a IS NOT NULL) THEN   IF (has_param IS FALSE)THEN  -- there is no param yet add WHERE to the query  query_where := ' WHERE ';   ELSE -- there is already something in the WHERE clause, we need to add AND
 query_where := query_where || ' AND ';   END IF;   query_where :=  query_where || 'parama='||a;   --beware if param quoting is required   has_param := TRUE; -- now there is at least 1 paramEND IF;
IF (b IS NOT NULL) THEN   IF (has_param IS FALSE)THEN  -- there is no param yet add WHERE to the query  query_where := ' WHERE ';   ELSE -- there is already something in the WHERE clause, we need to add AND
 query_where := query_where || ' AND ';   END IF;   query_where :=  query_where || 'paramb='||b;   --beware if param quoting is required   has_param := TRUE; -- now there is at least 1 paramEND IF;
--copy/paste/edit this IF ENDIF block for each paramquery_final := query_base || query_where;RAISE NOTICE '%', query_final;RETURN query_final;-- EXECUTE query_final;--END;$BODY$LANGUAGE 'plpgsql' VOLATILE;
test:select test(null,null)union select test(1,null)union select test(1,1)union select test(null,1);result:"SELECT * FROM my_table ""SELECT * FROM my_table  WHERE parama=1"
"SELECT * FROM my_table  WHERE parama=1 AND paramb=1""SELECT * FROM my_table  WHERE paramb=1"HTH,--MaXX