Thread: Help with optional parameters
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
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 quotingis 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 quotingis 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
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
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;
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 isinstead 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;> Ifyou 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