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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
