X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 563359FB326 for ; Thu, 17 Aug 2006 22:58:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 73020-10 for ; Fri, 18 Aug 2006 01:58:01 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.178]) by postgresql.org (Postfix) with ESMTP id 4A4269F95CB for ; Thu, 17 Aug 2006 22:58:01 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id n25so855296pyg for ; Thu, 17 Aug 2006 18:57:59 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=WhUJ+f6qq5Icf+TLxXWkWXhqMlkQlxnErAFovj5i+VuJZbUNLNsHEUwcFZBtPaqPz7Q2tWhYxK08iRE25UW/dzOqa6IkOP8MP3bRlJ3heH8ekLpl7uQag9/T+uMRFbSgCPQPSuiZlgyI3Rx897rPdtHJaDkvT//vR87iFaJGgxM= Received: by 10.35.117.5 with SMTP id u5mr4716475pym; Thu, 17 Aug 2006 18:57:59 -0700 (PDT) Received: by 10.35.131.14 with HTTP; Thu, 17 Aug 2006 18:57:59 -0700 (PDT) Message-ID: Date: Thu, 17 Aug 2006 18:57:59 -0700 From: "Rob Tester" To: MaXX Subject: Re: Help with optional parameters Cc: pgsql-sql@postgresql.org In-Reply-To: <44E466EF.5070308@skynet.be> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_15896_16702174.1155866279622" References: <44E466EF.5070308@skynet.be> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.574 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_30_40, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/141 X-Sequence-Number: 26099 ------=_Part_15896_16702174.1155866279622 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 > ------=_Part_15896_16702174.1155866279622 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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

------=_Part_15896_16702174.1155866279622--