X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 5A0D09FB32E
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 17 Aug 2006 00:39:56 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 51697-06 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 17 Aug 2006 00:39:51 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from py-out-1112.google.com (py-out-1112.google.com
 [64.233.166.179])
	by postgresql.org (Postfix) with ESMTP id B61599FB312
	for <pgsql-sql@postgresql.org>; Thu, 17 Aug 2006 00:39:51 -0300 (ADT)
Received: by py-out-1112.google.com with SMTP id n25so478173pyg
	for <pgsql-sql@postgresql.org>; Wed, 16 Aug 2006 20:39:50 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=FIXKDt7Qt+mLDl3jGGBKh1p8o0lk2H3nWM3VjxukvCuB3MuFWUpmaa6TooHMOZJMu2b0Y7waUD9rbekryUtAlugTKwhw8JXFn6WI3lYfuJbAzhi0jTfyMgSx0jtf7zEK9z/r39Cw6mwqxyV6GCedci1d38wHRDWeUwzN3F0meqs=
Received: by 10.35.49.1 with SMTP id b1mr2699088pyk;
	Wed, 16 Aug 2006 20:39:50 -0700 (PDT)
Received: by 10.35.131.14 with HTTP; Wed, 16 Aug 2006 20:39:49 -0700 (PDT)
Message-ID: <f5f60fb50608162039g59585cflbf75aebf0a137bc4@mail.gmail.com>
Date: Wed, 16 Aug 2006 20:39:49 -0700
From: "Rob Tester" <robtester@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Help with optional parameters
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_145015_15546308.1155785989967"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.696 tagged_above=0 required=5
	tests=DNS_FROM_RFC_ABUSE, HTML_40_50, HTML_MESSAGE, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200608/130
X-Sequence-Number: 26088

------=_Part_145015_15546308.1155785989967
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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?

------=_Part_145015_15546308.1155785989967
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div><font face="Arial" size="2">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&nbsp;NULL&nbsp;values.&nbsp;One way that will work (although extremely cumbersome) is to have a switch on the parameters to execute the correct query: 
</font></div>
<div><font face="Arial" size="2"></font>&nbsp;</div>
<div><font face="Arial" size="2">--This is a sample</font></div>
<div><font face="Arial" size="2">IF (a IS NULL&nbsp;AND b IS NULL) THEN</font></div>
<div><font face="Arial" size="2">&nbsp;&nbsp;&nbsp; select * from my_table;</font></div>
<div><font face="Arial" size="2">ELSEIF (a IS NOT NULL and b IS NULL) THEN</font></div>
<div><font face="Arial" size="2">&nbsp;&nbsp;&nbsp; select * from my_table where a=parama;</font></div>
<div><font face="Arial" size="2">ELSEIF (a IS NULL and b IS NOT NULL) THEN</font></div>
<div><font face="Arial" size="2">&nbsp;&nbsp;&nbsp; select * from my_table where b=paramb;</font></div>
<div><font face="Arial" size="2">ELSE</font></div>
<div><font face="Arial" size="2">&nbsp;&nbsp;&nbsp; select * from my_table where a=parama AND b=paramb;</font></div>
<div><font face="Arial" size="2">ENDIF;</font></div>
<div><font face="Arial" size="2"></font>&nbsp;</div>
<div><font face="Arial" size="2">This is extremely bad when you have 6 parameters giving 64 possible queries.</font></div>
<div><font face="Arial" size="2"></font>&nbsp;</div>
<div><font face="Arial" size="2">I tried using this (which works) but the planner likes to throw out the index for the columns because of the OR condition:</font></div>
<div><font face="Arial" size="2"></font>&nbsp;</div>
<div><font face="Arial" size="2">select * from my_table WHERE (parama IS NULL OR a=parama) AND (paramb IS NULL OR b=paramb);</font></div>
<div><span></span><font face="Arial" size="2"></font>&nbsp;</div>
<div><font face="Arial" size="2"></font>&nbsp;</div>
<div><font face="Arial" size="2">My next thought was to get the planner to think that using indexes would be a good thing so I did the following:</font></div>
<div><font face="Arial" size="2"></font>&nbsp;</div>
<div><font face="Arial" size="2">select * from my_table WHERE a=COALESCE(parama,a) AND b=COALESCE(paramb,b);</font></div>
<div><font face="Arial" size="2"></font>&nbsp;</div>
<div><font face="Arial" size="2">That works great unless the column value for a or b IS NULL in which case NULL&lt;&gt;NULL because it equals NULL.</font></div>
<div><font face="Arial" size="2"></font>&nbsp;</div>
<div><font face="Arial" size="2">Then I used the standby: set transform_null_equals to 1</font></div>
<div><font face="Arial" size="2"></font>&nbsp;</div>
<div><font face="Arial" size="2">This allows select null=null to return true.</font></div>
<div><font face="Arial" size="2"></font>&nbsp;</div>
<div><font face="Arial" size="2">However, I ran into the problem that&nbsp;a=a (when a is a NULL value) still equals NULL. But&nbsp;a=NULL is true. So it didn't work out.</font></div>
<div><font face="Arial" size="2"></font>&nbsp;</div>
<div><font face="Arial" size="2">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?</font></div>

------=_Part_145015_15546308.1155785989967--