Re: Help with optional parameters

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(at)skynet(dot)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
>