X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 025C49FB300 for ; Thu, 21 Sep 2006 19:19:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 63084-05 for ; Thu, 21 Sep 2006 19:19:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from gatekeeper.daycos.com (outbound.daycos.com [204.26.70.70]) by postgresql.org (Postfix) with ESMTP id 8BCE39FB2F0 for ; Thu, 21 Sep 2006 19:19:18 -0300 (ADT) Received: by gatekeeper.daycos.com with Internet Mail Service (5.5.2653.19) id ; Thu, 21 Sep 2006 17:18:49 -0500 Message-ID: <031936836C46D611BB1B00508BE7345D04AD244C@gatekeeper.daycos.com> From: Curtis Scheer To: pgsql-sql@postgresql.org Cc: Rob Tester Subject: Re: Help with optional parameters Date: Thu, 21 Sep 2006 17:18:48 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C6DDCB.E8F52F80" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.032 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200609/150 X-Sequence-Number: 26361 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C6DDCB.E8F52F80 Content-Type: text/plain 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; ------_=_NextPart_001_01C6DDCB.E8F52F80 Content-Type: text/html Content-Transfer-Encoding: quoted-printable

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 :=3D ‘SELECT * = FROM my_table WHERE 1 =3D1;

 

=

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 :=3D 'SELECT * FROM my_table ';
has_param :=3D FALSE;
query_where :=3D '';
IF (a IS NOT NULL) THEN
   IF (has_param IS FALSE)THEN
      -- there is no param yet add WHERE = to the query
      query_where :=3D ' WHERE ';
   ELSE
     -- there is already something in the WHERE = clause, we need to add AND
     query_where :=3D query_where || ' AND ';
   END IF;
   query_where :=3D  query_where || = 'parama=3D'||a;
   --beware if param quoting is required
   has_param :=3D TRUE; -- now there is at least 1 param
END IF;

 

=
------_=_NextPart_001_01C6DDCB.E8F52F80--