pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp with optional parameters
6+ messages / 4 participants
[nested] [flat]
* Help with optional parameters
@ 2006-08-17 03:39 Rob Tester <robtester@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Rob Tester @ 2006-08-17 03:39 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with optional parameters
@ 2006-08-17 05:21 Michael Fuhr <mike@fuhr.org>
parent: Rob Tester <robtester@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Michael Fuhr @ 2006-08-17 05:21 UTC (permalink / raw)
To: Rob Tester <robtester@gmail.com>; +Cc: pgsql-sql
On Wed, Aug 16, 2006 at 08:39:49PM -0700, Rob Tester wrote:
> 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?
Have you considered building a query string and using EXECUTE?
That's not as "neat" as a static query but it might be worth testing.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with optional parameters
@ 2006-08-17 12:54 MaXX <bs139412@skynet.be>
parent: Rob Tester <robtester@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: MaXX @ 2006-08-17 12:54 UTC (permalink / raw)
To: Rob Tester <robtester@gmail.com>; pgsql-sql
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$
DECLARE
a ALIAS FOR $1;
b ALIAS FOR $2;
-- add as many as input param
query_base text;
has_param bool;
query_where text;
query_final text;
BEGIN
query_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 param
END 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 param
END IF;
--copy/paste/edit this IF ENDIF block for each param
query_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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with optional parameters
@ 2006-08-18 01:57 Rob Tester <robtester@gmail.com>
parent: MaXX <bs139412@skynet.be>
0 siblings, 0 replies; 6+ messages in thread
From: Rob Tester @ 2006-08-18 01:57 UTC (permalink / raw)
To: MaXX <bs139412@skynet.be>; +Cc: pgsql-sql
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 <bs139412@skynet.be> 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$
> DECLARE
> a ALIAS FOR $1;
> b ALIAS FOR $2;
> -- add as many as input param
> query_base text;
> has_param bool;
> query_where text;
> query_final text;
>
> BEGIN
> query_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 param
> END 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 param
> END IF;
> --copy/paste/edit this IF ENDIF block for each param
> query_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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with optional parameters
@ 2006-09-21 22:18 Curtis Scheer <Curtis@DAYCOS.com>
0 siblings, 1 reply; 6+ messages in thread
From: Curtis Scheer @ 2006-09-21 22:18 UTC (permalink / raw)
To: pgsql-sql; +Cc: Rob Tester <robtester@gmail.com>
I noticed this one by searching in the archives, as I am working with some
"optional" parameters myself and noticed your solution. I just wanted to
make one improvement suggestion which is instead of checking whether or not
a parameter has been used simply start your query like so
Query_base := 'SELECT * FROM my_table WHERE 1 =1;
If you do that then you can just add on any parameters you need or not add
any at all. I think that seems to be a bit simpler than having a "has_param"
Boolean variable.
Thanks,
Curtis
_____
From: Rob Tester [mailto:robtester@gmail.com]
Sent: Thursday, August 17, 2006 8:58 PM
To: MaXX
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Help with optional parameters
BEGIN
query_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 param
END IF;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Help with optional parameters
@ 2006-09-21 22:48 MaXX <bs139412@skynet.be>
parent: Curtis Scheer <Curtis@DAYCOS.com>
0 siblings, 0 replies; 6+ messages in thread
From: MaXX @ 2006-09-21 22:48 UTC (permalink / raw)
To: pgsql-sql
Curtis Scheer wrote:
> I noticed this one by searching in the archives, as I am working with
some
> "optional" parameters myself and noticed your solution. I just wanted to
> make one improvement suggestion which is instead of checking whether
or not
> a parameter has been used simply start your query like so
> Query_base := 'SELECT * FROM my_table WHERE 1 =1;
> If you do that then you can just add on any parameters you need or
not add
> any at all. I think that seems to be a bit simpler than having a
has_param"
> Boolean variable.
True, I think I was too focused on the "clean" output, your solution
wastes less cycles and is more readable...
Snipet updated,
Thanks,
--
MaXX
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-09-21 22:48 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-17 03:39 Help with optional parameters Rob Tester <robtester@gmail.com>
2006-08-17 05:21 ` Michael Fuhr <mike@fuhr.org>
2006-08-17 12:54 ` MaXX <bs139412@skynet.be>
2006-08-18 01:57 ` Rob Tester <robtester@gmail.com>
2006-09-21 22:18 Re: Help with optional parameters Curtis Scheer <Curtis@DAYCOS.com>
2006-09-21 22:48 ` MaXX <bs139412@skynet.be>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox