Message-ID: <44E466EF.5070308@skynet.be>
Date: Thu, 17 Aug 2006 14:54:07 +0200
From: MaXX <bs139412@skynet.be>
User-Agent: Thunderbird 1.5.0.5 (X11/20060802)
MIME-Version: 1.0
To: Rob Tester <robtester@gmail.com>, pgsql-sql@postgresql.org
Subject: Re: Help with optional parameters
References: <f5f60fb50608162039g59585cflbf75aebf0a137bc4@mail.gmail.com>
In-Reply-To: <f5f60fb50608162039g59585cflbf75aebf0a137bc4@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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